How can a SQL Sever TSQL script tell what security permissions it has?

Question

I have a TSQL script that is used to set up a database as part of my product's installation. It takes a number of steps which all together take five minutes or so. Sometimes this script fails on the last step because the user running the script does not have sufficient rights to the database. In this case I would like the script to fail strait away. To do this I want the script to test what rights it has up front. Can anyone point me at a general purpose way of testing if the script is running with a particular security permission?

Edit: In the particular case I am looking at it is trying to do a backup, but I have had other things go wrong and was hoping for a general purpose solution.

Answer 1

I assume it is failing on an update or insert after a long series of selects.

Just try a simple update or insert inside a transaction. Hard-code the row id, or whatever to make it simple and fast.

Don't commit the transaction--instead roll it back.

If you don't have rights to do the insert or update, this should fail. If you DO, it will roll back and not cause a permanent change.

Answer 2

try the last insert/update up front with some where condition like

insert/update
where 1=2

if (@@error <> 0)
   raise error 6666 'no permissions'

this would not cause any harm but would raise a flag upfront about the lack of rights.

Answer 3

select * from fn_my_permissions(NULL, 'SERVER')

This gives you a list of persmissions the current session has on the server

select * from fn_my_permissions(NULL, 'DATABASE')

This gives you a list of permissions for the current session on the current database.

http://msdn.microsoft.com/en-us/library/ms176097(SQL.90).aspx see here for more information.