by Bill Wunder

System or meta-information can be divided into system level information and database level information. In general if you want something at he server level you can find the details in tables in the master or msdb databases. If you want to know about something at the database level you can look in that database.

Permissions can be divided into object permissions and statement permissions. Object permissions include Select, Insert, Update, Delete, Execute and the obscure References. Statement permissions indicate a user's ability to CREATE databases and database objects. BACKUP is another statement that can be provisioned to users through Statement permissions. There are other statements that can be expected only by certain users such as ALTER and TRUNCATE, but the ability to use these other statements cannot be granted or transferred as can CREATE and BACKUP permissions. Generally in SQL Server 2000 there is limited use of statement permission. Instead most access control is implemented through object permissions.

The permissions changes coming with SQL Server 2005 may give us pause to rethink some of the standard permissions practices currently in use. For example, in SQL Server 2000 a user owned objects. In SQL Server 2005 a user will own - and can share ownership of - schema and the schema will have objects associated with it. At the surface this user-schema separation is not a huge change. If the user is dbo and the schema is dbo then even the new semantics of the change is difficult to see. On the other hand if the schema is sales and the schema owner is bill it follows that when I log in as bill I can execute sales.AddOrderItem. If sue is also an owner of the sales schema, she too can execute sales.AddOrderItem. In fact, if I'm reading the YUKON Beta 1 documentation correctly, sue and I can both execute sales.AddOrderItem without the need to provide the schema name! When we say exec AddOrderItem, SQL Server 2005 will know we mean sales.AddOrderItem. That's different than both of us simply having EXECUTE permission for a couple of reasons. First owner and schema mappings can change without the need to make any application changes and as well we now have a way to manage ownership chains transparently to users. This is a big deal because it opens the door for us to not map everything through the dbo schema in order to assure common availability. If we don't have to use dbo, we can gain full control over who can and cannot do stuff in a database. Combined with another new feature of SQL Server 2005, EXECUTE AS, user-schema separation promises to turn everything we had accepted as the best way to secure our data upside down. Clearly it is important for us to keep an eye toward the changes coming in SQL Server 2005. In the mean time, it will be useful to gain a full understanding about the way things work in SQL Server 2000. One good way to do that is to become fluent in traversing system tables.

Getting back to SQL Server 2000, Examining object permissions metadata requires that you look at both the server level and the database level. At the server level, anyone that connects to the server must have a valid SQL Server login and must be granted access to each database containing user data they might access. At the database level the object permissions are assigned to the local user associated with that login or to a role to which the login gains access when inside the database Rather that a "this table, that table, this table, that table" narrative that gets confusing and tends to induce drowsiness, allow me to use an example to describe the inter-workings of the system level and database level system tables for this permissions context.

Let's define a goal of extracting all the object permissions from a database. To give the goal some real world usefulness lets generate DDL that can fully and accurately reproduce the object permissions in that database.

The first thing we need for the objective script to work is a set of statements to add the logons. Notice I'm reading from the system tables here. I'm not going to try to stuff data back into the system tables. Instead I will use the proper system stored procedures. For SQL logins I generate sp_addlogin statements, leaving a placeholder for passwords and extracting the SID so the login script will create a login that will correctly map to the database user(s) as defined on the SQL Server where the script is being generated . Actually the passwords are stored in SQL Server's encrypted form in master.dbo.sysxlogins, however sp_addlogin will not accept the encrypted form. Notice in dbo.sysusers I can filter to make sure I get only logins and exclude roles and also that I can distinguish between SQL logins and NT logins. I'm filtering dbo from the generated sp_grantlogin statements because it simply doesn't make sense to add dbo to a database it already owns. That's more an example of understanding how the data works than of understanding the system tables. 

That is almost all that is necessary to include the server level components - logins - with the object permissions in this database. There remains a need for a little more system level data as you will see below. The next thing that needs to happen in the creation order is to add the uses to the database. Again, as with the logins, a different system stored procedure is needed to ass SQL users and Windows users to the database. And, as with the logins, I am able to determine which statement a user ought to be placed with by filtering on columns in dbo.sysuser. Again I filter out dbo and also guest since, as before, it makes no sense to add these users to a database.

select cast('if (select user_id(''' + name + ''')) is null   
 exec sp_adduser [' + name + ']' as varchar(120)) as [-- Add users script]
from dbo.sysusers 
where islogin = 1
and isntname = 0
and name not in ('dbo','guest')
union
select cast('exec sp_grantdbaccess [' + name + ']' as varchar(120)) as [-- Add users script] 
from dbo.sysusers 
where islogin = 1
and isntname = 1
and name not in ('dbo')

Next I need to add the roles. In SQL Server 2000 the groupuids less than 16400 are reserved for the built-in roles. As with dbo or guest, all we'd get if we tried to add a built-in role would be an error message so we exclude them from processing here. Once that we have the roles we can add the role members. I don't exclude the built-in roles while building the list of role members to add, only when building the list of roles to add. Otherwise I could miss some important permissions  dbo.sysmembers is a  narrow table containing only a column for groups and a column for members. We could have joined these user Ids back to dbo.sysusers to achieve the same result as we got from the user_name()  system function. I resisted here to help you resist the tendency to think that direct access to system tables is THE way to get the data you need. I think the general rule of thumb ought to be, “if there is no other way then consider the system tables.” 

select distinct 'if (select user_id(''' + cast(user_name(groupuid) as varchar(50))+ ''')) is null
 exec sp_addrole [' + cast(user_name(groupuid) as varchar(50)) + ']' as [-- Add roles script]
from dbo.sysmembers
where groupuid > 16399

select 'exec sp_addrolemember [' +
 cast(user_name(groupuid) as varchar(50)) + '], [' +
 cast (user_name(memberuid) as varchar(50)) + ']' as [-- Add role members script]
from dbo.sysmembers
where user_name(memberuid) not in ('dbo','guest') 

That gives everything we need to know to recreate the logins, users, roles and role members in a database and brings us to the business of actually getting the object permissions for the database. Here's where we will need to go back to the server level, but only to decode some of the cryptic data stored at the database level. In the master database we find the table master.dbo.spt_values that serves the purpose of a mapping or lookup table for many other system tables. In most cases it is necessary to provide a character code that matches a a range of rows via an a nchar(6) literal in master.dbo.spt_values and an integral value to identify a specific row in that character codes subset to retrieve a common character label. For example joining the number column for code type "L" rows in master.dbo.spt_values to the req_mode column in master.dbo.syslockinfo will tell you the type of lock held by the resource identified in the lock or, as in the following query, joining the protecttype value from  dbo.sysprotects table in a database to the correct subset in master.dbo.spt_values will produce a Grant or Deny literal as appropriate and joining the action column from  dbo.sysprotects table in a database to the same subset in master.dbo.spt_values will produce the Execute literal we need to build a valid statement. Using the lookup table it is possible to produce valid permissions statements from the numeric columns stored in dbo.sysprotects.

select  cast(type.name as varchar(10)) + ' ' +
 cast(action.name as varchar(20)) + ' ON [' +
 cast(user_name(o.uid) as varchar(20)) + '].[' +
 cast(o.name as varchar(50)) + '] TO [' + 
 cast(user_name(p.uid) as varchar(80)) + ']' as [-- Procedure Permissions]
from dbo.sysprotects p
inner join dbo.sysobjects o
on p.id = o.id
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
where type.type = 'T'
and action.type = 'T'
and o.Type = 'P'
and o.status > 0
order by o.type, o.name

Similarly we can resolve the numeric data in dbo.sysprotects to Grant or Deny of Select, Insert, Update, Delete and References permissions on the tables in a database. This query does not resolve to column level permissions though it does make sure that if column level permissions exist a script will not be generated that changes the column level permissions to table level permissions. If you want to see the hard core truth about identifying column level permission take a look at the source for the system stored procedure sp_helprotect located in the master database.

That is a point worth repeating. Looking at the source of system stored procedures can help you understand system tables and I promise you will pick up a trick or two along the way as you slog through the system stored procedures. Be forewarned that the system stored procedures are not quite as rich with comments as I'd like to see from my developers.

select  cast(type.name as varchar(10)) + ' ' +
 cast(action.name as varchar(20)) + ' ON [' +
 cast(user_name(o.uid) as varchar(20)) + '].[' +
 cast(o.name as varchar(50)) + '] TO [' + 
 cast(user_name(p.uid) as varchar(80)) + ']' as [-- Table permissions]
from dbo.sysprotects p
inner join dbo.sysobjects o
on p.id = o.id
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
where type.type = 'T'
and action.type = 'T'
and o.Type = 'U'
and o.status > 0
and p.columns = 0x1
order by o.type, o.name

Using the same approach we used for procedures and tables, you can also pick up the permissions for other database objects. To make a long story short, you can see a listing if the object type in master.dbo.spt_values with a query like:

    select * from master..spt_values where type = 'O9T'

Then you can feed the ones interesting to into as query like the one above to get the permissions you wish to document.

Looking at permissions shows a good cross sectional assortment of system tables. We can see that some system tables are at the server level, some at the database level. Of course you don't want to make your living in the system tables. It's not good to build  system table dependencies into your application because we have no way of knowing when the system table might change in such a way to break your application. On the other hand, knowing your way around the system tables is invaluable to a busy DBA.

Next week I'll write a bit about some tools to help you understand the many other system tables beyond the few we used here to identify object permissions, explore some of the metadata tools available that are better used by applications than direct access to system tables, and cover the extreme case of why you should never modify systems tables and how to do it if you absolutely must..

Bill