check for ALTER DB permissions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

check for ALTER DB permissions

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2006-11-27 : 12:58:08

What's the quickest way (using a script) to check to see if a specific user can set a specific database into SINGLE_USER mode? I'm not sure how to use the PERMISSIONS() function to do so, and I figure there's a faster way than seeing if the user is in db_owner or dbcreator or sysadmins.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-27 : 13:15:40

check out system stored procedures in Books Online. Specifically:

sp_helpuser
sp_helprole
sp_helpsrvrole
sp_helprolemember
sp_helpsrvrolemember

Be One with the Optimizer
TG

EDIT:

From Books Onlines (Alter Database):

quote:
ALTER DATABASE permissions default to members of the sysadmin and dbcreator fixed server roles, and to members of the db_owner fixed database roles. These permissions are not transferable.

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2006-11-27 : 13:28:09

Thanks TG. I looked at those, but I don't think they cover all the possibilities. The user of the app I'm working on can connect either using Windows credentials or SQL credentials, and with the latter, might only be a sysadmin and therefore not have an associated user. If the sp's you mentioned cover all those possibilities, I don't understand how.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-27 : 15:08:07

I believe these will show all sql db users as well as windows logins that belong to the 3 roles that are capable of altering a database.

exec master..sp_helpsrvrolemember sysadmin
exec master..sp_helpsrvrolemember dbcreator
exec <myDatabase>..sp_helprolemember db_owner

Be One with the Optimizer
TG

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2006-11-27 : 15:16:13

The problem with that is that if Windows authentication is used and the user is in a SQL group that has permissions but the Windows user itself does not have permissions by its name, then I have to query Active Directory to see what groups the user is in, which definitely seems like overkill.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-27 : 15:58:39

Sorry Influent. I just re-read your original post and see that you wanted alternatives to the suggestions I gave.

I don't don't know of a "simple" way to do this. The function you mentioned: permissions() doesn't specifically work for ALTER DATABASE so that doesn't look like an option. sysprotects and sp_helprotect doesn't appear to specify permissions for alter database either.

ALTER DATABASE doesn't sound like something you would want anyone but DBAs and sysadmins to be able to do anyway. Are you trying to prevent this from happening? If so it sounds like you need to tighten up security and isolate your "application users" from the windows groups that are members of these three sql server roles.

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-27 : 17:47:34

There are:
xp_enumgroups
xp_logininfo

these can be used to query for NT groups and their members.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources