SQL Stuff

Find Granted Permissions Per User

Here’s some quick SQL logic to get a list of permissions per account and roles that were granted per account.

If you are dropping an account with for example: drop login [int\myaccount], and you run into an error where the account was used to grant permissions to some other database object, then you’ll need to go about finding those permissions, but I would not recommend using the GUI to determine where those grants were done.

To find SQL GRANTS which were explicitly given per account.

use master;
set nocount on

'account name' = servprin.name
,	'type' = servprin.type_desc
,	'permission' = servperm.permission_name
,	'state' = servperm.state_desc
sys.server_principals servprin
join sys.server_permissions servperm
on servprin.principal_id = servperm.grantee_principal_id
servprin.type in ('s', 'u', 'g')
--and servprin.name like '%chris%'
order by
'account name' asc

If you want to change the database owner to something ( for example ‘sa’ ), you can run the following logic.

use master;
set nocount on
declare	@change_database_owner		varchar(max)
set	@change_database_owner		= ''
select	@change_database_owner		= @change_database_owner + 
'exec [' + name + ']..sp_changedbowner ''sa'';' + char(10)
from	sys.databases where name not in ('tempdb') order by name asc
exec	(@change_database_owner)

Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s