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 select 'account name' = servprin.name , 'type' = servprin.type_desc , 'permission' = servperm.permission_name , 'state' = servperm.state_desc from sys.server_principals servprin join sys.server_permissions servperm on servprin.principal_id = servperm.grantee_principal_id where 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