CODEWITHSUNDEEP
sqlsql-servert-sqlpermissions
Paolo B
Paolo B

Reputation: 3354

SQL Server - Query to view permissions on every table in database

We are using DB project to track Db changes and a standard XML file to publish the changes locally, to test server, Production etc. At the moment we are just copy pasting code like below to Grant permissions to new tables we create.

GRANT SELECT
    ON [dbo].[OrganisationSite] TO [Company_FullAccess]
    AS [dbo];
GO
GRANT UPDATE
    ON [dbo].[OrganisationSite] TO [Company_FullAccess]
    AS [dbo];
GO
GRANT INSERT
    ON [dbo].[OrganisationSite] TO [Company_FullAccess]
    AS [dbo];
GO
GRANT DELETE
    ON [dbo].[OrganisationSite] TO [Company_FullAccess]
    AS [dbo];
GO

This is prone to error as we could forget to grant a specific permission.

Is there any way to create a SQL query, to view the permissions granted for each table in the database? So we can check to see what permissions have been granted.

Also, any advice on making this more robust would be appreciated.

Upvotes: 1

Views: 275

Answers (1)

Ilyes
Ilyes

Reputation: 14928

The best idea in this case is to use ROLE, and add members as needed.

From the documentation:

Roles are database-level securables. After you create a role, configure the database-level permissions of the role by using GRANT, DENY, and REVOKE

That will make it easy after you create ROLES as needed, you can just add members on it, no need to check every member permissions.

Upvotes: 2

Related Questions