Hello people!
Good day.
In this post, I will demonstrate how to verify the permissions of a user and / or database in SQL Server, as well as allowing you to easily remove these access or grant again. If you want to replicate a user's permissions between different instances, see if the article SQL Server - How to copy / replicate a user's permissions can help you.
Using system SP, sp_helprotect
Using the stored procedure sp_helprotect, we can easily list the permissions of a particular user or object:
1 2 3 | -- Verificando as permissões do usuário "Usuario_Teste" EXEC Protheus_Producao.dbo.sp_helprotect @username = 'Usuario_Teste' |
1 2 3 | -- Verificando as permissões da tabela "SA1010" EXEC Protheus_Producao.dbo.sp_helprotect @name = 'SA1010' |
Using Transact SQL Queries in Catalog Views
A very interesting way to get and work with this information that we saw in the SQL Server Managment Studio interface is by using SQL queries and SQL Server database catalog views. These queries bring more information than the sp_helprotect system sp and just like using SP we can work with the returned data.
View sourceUsing a Custom Stored Procedure
With a little more work and refinement, we can use a Stored Procedure for this purpose. We can use feedback to generate an access report, record the information for history, etc. See how easy it is:
View SP source codeUsage examples
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | -- Verifica todas as permissões do usuário 'Usuario_Teste' na instância EXEC dbo.stpVerifica_Permissoes @Ds_Usuario = 'Usuario_Teste' -- Verifica todas as permissões da tabela 'SA1010' no database 'Protheus_Producao' EXEC dbo.stpVerifica_Permissoes @Ds_Database = 'Protheus_Producao', @Ds_Objeto = 'SA1010' -- Verifica as roles de database do usuário 'Usuario_Teste' em todos os bancos EXEC dbo.stpVerifica_Permissoes @Ds_Usuario = 'Usuario_Teste', -- varchar(100) @Ds_Database = NULL, -- varchar(100) @Ds_Objeto = NULL, @Nr_Tipo_Permissao = 1, @Fl_Permissoes_Servidor = 0 -- Não -- Verifica as permissões a nível de Database do usuário 'Usuario_Teste' EXEC dbo.stpVerifica_Permissoes @Ds_Usuario = 'Usuario_Teste', -- varchar(100) @Ds_Database = NULL, -- varchar(100) @Ds_Objeto = NULL, @Nr_Tipo_Permissao = 2, @Fl_Permissoes_Servidor = 0 -- Não -- Verifica as permissões do database 'Protheus_Producao' para todos os usuários EXEC dbo.stpVerifica_Permissoes @Ds_Usuario = NULL, -- varchar(100) @Ds_Database = 'Protheus_Producao', -- varchar(100) @Ds_Objeto = NULL, @Nr_Tipo_Permissao = 2, @Fl_Permissoes_Servidor = 0 -- Não -- Verifica as permissões a nível de sistema da instância EXEC dbo.stpVerifica_Permissoes @Nr_Tipo_Permissao = 4 -- Verifica os membros de roles de sistema da instância EXEC dbo.stpVerifica_Permissoes @Nr_Tipo_Permissao = 3 |
Return Example (First Example)
Using a Stored Procedure with GRANT and REVOKE Code
Like the previous Stored Procedure, this version is a little more "compact" and still generates the T-SQL codes to grant this privilege (if you want to make a backup of the existing permissions) and also the T-SQL code to remove this permission.
View stp's codeVerify_User_PermissionsExamples of use:
1 2 3 4 5 6 7 | -- Verifica as permissões de todos os usuários do database "dirceuresende" EXEC dbo.stpVerifica_Permissoes_Usuario @Ds_Database = 'dirceuresende', -- varchar(50) @Ds_Usuario = '', -- varchar(50) @Fl_Objetos_Sistema = 0, -- bit @Fl_Permissoes_Instancia = 0, -- bit @Fl_Permissoes_Banco = 1 -- bit |
1 2 3 4 5 6 7 | -- Verifica as permissões do usuário "teste" em todos os databases EXEC dbo.stpVerifica_Permissoes_Usuario @Ds_Database = NULL, -- varchar(50) @Ds_Usuario = 'teste', -- varchar(50) @Fl_Objetos_Sistema = 0, -- bit @Fl_Permissoes_Instancia = 0, -- bit @Fl_Permissoes_Banco = 1 -- bit |
1 2 3 4 5 6 7 | -- Verifica todas as permissões dos usuários "%teste%" em todos os databases e a nível de instância EXEC dbo.stpVerifica_Permissoes_Usuario @Ds_Database = NULL, -- varchar(50) @Ds_Usuario = '%teste%', -- varchar(50) @Fl_Objetos_Sistema = 0, -- bit @Fl_Permissoes_Instancia = 1, -- bit @Fl_Permissoes_Banco = 1 -- bit |
1 2 3 4 5 6 7 | -- Verifica todas as permissões, de todos os usuários da instância EXEC dbo.stpVerifica_Permissoes_Usuario @Ds_Database = NULL, -- varchar(50) @Ds_Usuario = NULL, -- varchar(50) @Fl_Objetos_Sistema = 1, -- bit @Fl_Permissoes_Instancia = 1, -- bit @Fl_Permissoes_Banco = 1 -- bit |
Using a Query
Another quick and convenient alternative to listing user permissions is by using a multi-UNION query to return role, system, and object permissions.
View query source codeUsing the SQL Server Managment Studio Interface (GUI)
A very easy and practical way to check user permissions is to use the SQL Server Management Studio interface itself for this. In my view, the biggest problem with this solution is that you are dependent on the tool and we know that a DBA cannot rely solely on the interface for your day to day. Another problem I see with this solution is that you can't work with this information and use it for some automated routine, for example.
In this screen we can view the user-owned schemas and database-level roles that this user is part of (See more about database roles here).
In this screen, we can list the permissions granted on each database object and so we have this view by user and object.
In this screen we can set database level permissions for a given user. These permissions apply to all objects in this database that this privilege encompasses, as well as maintenance permissions on the database (eg backup, restore, checkpoint, etc.).
Server-level permissions - Server roles
In this screen, you can identify which server-level roles the user in question is part of. (see more about server-side roles here)
Server-level permissions - User Mapping
In this screen you can identify which databases a particular Login has a user created in and which database roles they are part of.
Server-level permissions - Securables
In this one, we can view permissions on instance maintenance activities, such as Shutdown, Create any login, Create any database, etc.
That's it folks!
I hope you enjoyed the post!
sql server list view report user system role object table view permission permissions list view report permissions access user users
sql server list view report user system role object table view permission permissions list view report permissions access user users
Great procedure! Very useful !!! I just made a change that was not bringing the name of the schemas, but now I saw that you published a version already bringing this !!! Perfect!
Great post ..
Congratulations .. Liking your blog a lot!
André,
Thanks so much for the feedback! This is very important.