Bits N Bytes

Finding impersonation info in SQL Server

Since SQL Server 2005 we’ve been able to grant one login the ability to impersonate another in order to have a different set of effective permissions.  More than a few times I’ve found this paradigm useful in order to solve problems while adhering to the principal of least privilege and likewise more than a few times I’ve tried to find information about who has impersonation privileges in SSMS and have been severely disappointed to find that it is buried.  This extremely simple script solves that problem and gives all of the information about who has impersonation rights on an instance.


If you want to read more about all the fantastic things you can do with EXECUTE AS and impersonation, MSDN is a good place to start

SELECT AS WhoCanImpersonate ,
        grantee_principal.type_desc AS ImpersonatorType , AS WhoCanTheyImpersonate ,
        sp.type_desc AS ImpersonateeLoginType

FROM    sys.server_permissions AS prmssn
        INNER JOIN sys.server_principals AS sp
            ON sp.principal_id = prmssn.major_id
               AND prmssn.class = 101
        INNER JOIN sys.server_principals AS grantee_principal
            ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE   prmssn.state = 'G'

Database Automagic

This months TSQL Tuesday is hosted by a good friend Pat right over at  SQL Asylum

For this months entry I decided to keep it short and sweet, following in my Bits N Bytes theme.

The Meta Script

In the true sense of the word automation, this really doesn’t fit but, in the terms of quickly getting something done that would otherwise be a mundane repetitive task, this can save a world of time.

Lets say we have a list of objects in the Sales Schema and we have a request to grant Select and Insert access to a user for those objects.  There are two approaches, 1 is to grant select and insert to the actual schema like this


However you might decide that you only want to grant direct SELECT and INSERT on the tables that exist in the DBO Schema today not those tables which may be created in the future (auditors love to make us do this)

A simple way to automate granting these rights is by writing a script that writes a script like so

‘GRANT SELECT, INSERT ON ‘ + + ‘.’ + + ‘ TO BusinessUser’
   FROM sys.all_objects obj JOIN sys.schemas sch

    ON obj.schema_id = sch.schema_id

  WHERE = ‘Sales’
  and obj.type = ‘U’

This should give you a result set that looks something like the following:

GRANT SELECT, INSERT ON Sales.People TO BusinessUser
GRANT SELECT, INSERT ON Sales.Sales TO BusinessUser

At this point, run the output in a separate command window and viola you’ve automated that grant of permissions

This may not be true “automation” in the sense that Pat was looking for but, perfecting the ability to write scripts that write scripts is a huge timesaver

Changing the owner of nearly anything in SQL Server

The other day we were going through some old scripts and had a huge discussion about changing object ownership in SQL.  Forever the only answers were to recreate the objects or use the stored procedures: sp_changeobjectowner or sp_changedbowner.  Alternatively at times there were those of us who used some other undisclosed hack of system tables but, I’ve never done anything like that…

Since SQL 2005 Alter Authorization has been the right way to change ownership of nearly anything

This is the most common use of alter authorization but there are many more.

sp_changeobjectowner would look like this

sp_changeobjectowner 'Products.Description','AllenK'

While the updated syntax would look like this

ALTER AUTHORIZATION ON OBJECT::[Products].[Description] TO [AllenK]

What really brought this post about was because I was working with certificates recently and at the time I needed to change who the owner and I just exported/imported & recreated them.  It was easy and what I knew would work.  After looking through the syntax for alter authorization I realized I could & probably should have done it this way.  I guess old habits die hard.


Some out there might question why I’m rehashing syntax changes that happened nearly 5 years ago, The reality is that even in 2011 a lot of the installations of SQL Server I look at are still 2005 and many of the people I run into still use old ways of doing things, this is for you (and me)!

ALTER AUTHORIZATION ON OBJECT::[Products].[Description] TO [AllenK]

Something old is something new

For the new year I have decided to try something different with a series of small regular posts.

Dealing with our large team of developers and our DBA team as well, I often run across interesting discussions about small things that are easily overlooked.  Often times these discussions end in a exclamation of “I knew that!!”  Of course what really happend is the memory of that specific feature or tidbit has simply been paged out of our memory.  I intend to take these (typicaly) small tidbits of SQL goodness and publish them to hopefully jog your memory as well. 

It never hurts to have your memory paged back in occasionally and I hope this helps

Go to Top