December 3, 2015 at 11:30 am
I'm new to SQL Server, and I'm trying to understand the privileges available to logins, and I'm having trouble with what I'm calling ANSI 92 privileges (although they might officially belong to a better named group).
I have a user account that I don't want to make sysadmin, and so I found that I can grant this account the CREATE ANY DATABASE privilege. So, this particular privilege isn't available if you use SSMS and walk through the right-click menu. That's fine. So, I grant this privilege by executing a 'grant CREATE ANY DATABASE to <login>'. No problem.
Since the SSMS GUI didn't allow me to grant this particular privilege, neither will it display it when I examine that particular user's privileges through the Properties menu, nor does it include it if you go through the "Script Logon as->" process.
So I have a couple of questions:
1) how do I generate a list of all privileges provided to a user?
2) is there a better nomenclature I should be using to differentiate between these types of privileges that seem to break on MSSQL native privileges, vs these ANSI 92 privileges?
Thanks,
--=Chuck
December 3, 2015 at 2:28 pm
SSMS is a client, not SQL Server :). SSMS happens to ship with SQL Server, but it is not built by the SQL Server's developers. It is instead built by Visual Studio developers, and it is gradually being decoupled from SQL Server. For example, the latest SSMS beta version is available at Microsoft.com/downloads.
I am unsure why you must stick with ANSI-92 (every RDBMS vendor has more than one edge over it), but for this response I will stick with SQL Server's views :).
For 1), there is sys.database_principals, sys.server_principals, sys.database_permissions, sys.server_permissions, sys.database_role_members, and sys.server_role_members. I am fairly certain this site offers more than one script that will fetch/script out permissions.
For 2), I use SQL Server terminology, but I have the luxury of not needing to translate :).
December 3, 2015 at 2:51 pm
In re-reading that post, I realize what a vaguely worded question I asked.
In actuality, I'm looking to gather some experiences on the differences between, for example, granting
dbcreator
vs.
both 'CREATE ANY DATABASE' and 'DROP ANY DATABASE'
Maybe these are the outlier roles, and there are in actuality few which seem to have some overlap.
In my case granting only CREATE ANY DATABASE (and omitting DROP ANY DATABASE) works better than granting dbcreator, since I don't want this account to be able to drop any databases (although I understand that it can drop any database it owns, until I get a chance to change that ownership).
It just caught me off guard when I was searching for server-level privileges that there was a mix of 'db%' privilege names alongside pretty syntactically different names like 'DROP%' and 'CREATE%'.
I'm thinking it's just an evolution in the capabilities of SQL that I just need to get accustomed to, and that I just need to grant the privileges that best fit the situation, as opposed to trying to stick with role names which fit the older syntax vs the newer?
December 4, 2015 at 7:59 am
chuck.forbes (12/3/2015)
It just caught me off guard when I was searching for server-level privileges that there was a mix of 'db%' privilege names alongside pretty syntactically different names like 'DROP%' and 'CREATE%'.
I'm thinking it's just an evolution in the capabilities of SQL that I just need to get accustomed to, and that I just need to grant the privileges that best fit the situation, as opposed to trying to stick with role names which fit the older syntax vs the newer?
Yes, you are correct. You should also be investigating user defined Server roles which were introduced in SQL Server 2012
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2015 at 11:46 am
chuck.forbes (12/3/2015)
In re-reading that post, I realize what a vaguely worded question I asked.In actuality, I'm looking to gather some experiences on the differences between, for example, granting
dbcreator
vs.
both 'CREATE ANY DATABASE' and 'DROP ANY DATABASE'
Maybe these are the outlier roles, and there are in actuality few which seem to have some overlap.
In my case granting only CREATE ANY DATABASE (and omitting DROP ANY DATABASE) works better than granting dbcreator, since I don't want this account to be able to drop any databases (although I understand that it can drop any database it owns, until I get a chance to change that ownership).
It just caught me off guard when I was searching for server-level privileges that there was a mix of 'db%' privilege names alongside pretty syntactically different names like 'DROP%' and 'CREATE%'.
I'm thinking it's just an evolution in the capabilities of SQL that I just need to get accustomed to, and that I just need to grant the privileges that best fit the situation, as opposed to trying to stick with role names which fit the older syntax vs the newer?
CREATE ANY DATABASE is a specific permission which you can grant/deny to a login or role.
dbcreator is a fixed server role, this has predefined granted/denied permissions such as CREATE ANY DATABASE assigned to it. Members of the role inherit these permissions - you simply control membership.
December 4, 2015 at 11:57 am
SSCrazy,
So do the fixed server roles actually have these actual permissions listed as part of their make-up, or are those 'permissions' just defined in the documentation? For example, in Oracle there are built in server-level roles like RESOURCE. If you look at that role, you would see that it actually contained the privileges CREATE TABLE, CREATE VIEW, etc.
--=Chuck
December 4, 2015 at 12:36 pm
Beyond here https://msdn.microsoft.com/en-us/library/ms188659(v=sql.110).aspx, I don't think you can list fixed server role permissions from with SQL.
I suspect because fixed role permissions are checked slightly differently within some parts of SQL server's code.
If you create your own server roles you can view their permissions in sys.server_permissions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply