CODEWITHSUNDEEP
sqlsql-serversecurityt-sql
Martin Brown
Martin Brown

Reputation: 25310

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

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.

Upvotes: 5

Views: 1557

Answers (3)

Kjartan Þór Kjartansson
Kjartan Þór Kjartansson

Reputation: 555

select * from fn_my_permissions(NULL, 'SERVER')

This gives you a list of permissions 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.

See here for more information.

Upvotes: 4

Learning
Learning

Reputation: 8175

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.

Upvotes: 0

BradC
BradC

Reputation: 39956

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.

Upvotes: 0

Related Questions