SQL Server How to assign schema/table access to a few people

pftdvrlh  于 2023-04-04  发布在  其他
关注(0)|答案(1)|浏览(113)

I have a few tracking tables in the SQL Server database which track DDL changes. How do I restrict access to that table so no one has access to that schema and tables.

Would creating a role help? whoever has access to that role can see that schema/tables?

gudnpqoy

gudnpqoy1#

The solution is to create a login with the default database and a user in the database linked to this login with the default schema that you want and grant the privileges required by your application.

As an example :

USE master;
GO

CREATE LOGIN CNX_foo 
   WITH PASSWORD = 'abracadabra2023!',
        DEFAULT_DATABASE = MyDB;
GO

USE MyDB;
GO

CREATE USER USR_foo
   FROM LOGIN CNX_foo;
GO

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES
   TO USR_foo;
GO

DENY SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES ON SCHEMA::Trace
   TO USR_foo;

相关问题