March Madness – SQL Server System Tables – sysusers

March Madness continues and today we are going to talk about the sysusers system table. Unlike yesterday’s system table (syslogins) which was an instance level table, the sysusers table exists inside of every database on your instance. Most new DBAs are unaware of the system tables, and even more unaware that some tables are scoped at the instance level and others are scoped at the database level.

Please note that this system table will be removed from a future version of SQL Server.

What is this table for?

This table will contain one row for every user in the database. The user can be a Windows login or Windows Group, a SQL Server login, or even a SQL Server database role. Go ahead and run it for yourself (please note that this returns information for the database you are currently connected to):

SELECT *
FROM sysusers

Why should you care?

As with yesterday’s post, you care about this system table for disaster recovery purposes or for restoring permissions to a database after a restore between servers. One thing I liked to do was to grab the name of the user from sysusers and pass it to the system stored procedure sp_helpusers in a method similar to this:

DECLARE usr_name CURSOR READ_ONLY FOR
SELECT name
FROM sysusers
WHERE hasdbaccess = 1
DECLARE @name sysname
OPEN usr_name

FETCH NEXT FROM usr_name INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
 EXEC sp_helpuser @name
 PRINT @name
 END
 FETCH NEXT FROM usr_name INTO @name
END

CLOSE usr_name
DEALLOCATE usr_name
GO

This way I could put permissions back exactly as they were before the disaster or restore. Please note that this method will only capture details for the user and the associated database roles they are a member of and it will not detect explicit GRANT/DENY statement. Ideally you would be able to tie all permissions to database roles in order to simplify permissions and security, but sometimes that is not always possible. In other words, use the code above at your own risk and don’t come yelling at me if it didn’t work as expected simply because you didn’t know enough about what you needed to have done.

Another reason for caring is similar to yesterday: troubleshooting. It helps to show people that permissions have not changed since yesterday. And you can only do that if you are capturing these details.

Where else is this information?

Remember how I said this system table will be removed from a future version of SQL Server? Then you should look to start using the sys.database_principals security catalog view instead. I mean it. Get started now. You’ll thank me later.