March Madness – SQL Azure – sys.sysusers

Yesterday we started this blog post series with a look at the sys.sql_logins system view in SQL Azure. Today we will look inside the user information found in the sys.sysusers table.

Unlike the sys.sql_logins view which is found only in the master database, the sys.sysusers view is in each database. After connecting to your SQL Azure instance you can execute the following:

SELECT *
FROM sys.sysusers

What is this system view for?

This table will contain one row for every user in the database. The user names returned can be a SQL Server login or a SQL Azure database role. I know, not very exciting yet, right? Well then, go ahead and execute that code against the master database and tell me if you find anything interesting.

When I ran that code for the first time two names jumped out at me: dbmanager and loginmanager. They are not users, they are database roles inside of the master database. The loginmanager role is similar to the securityadmin fixed server role found in SQL Server and members of this role can create new logins. The dbmanager role would be like the dbcreator fixed server role and members of this role would be able to create a new SQL Azure database. Only users in the master database are allowed to be members of these roles.

Why should you care?

A typical use for the sysusers table is to capture information to help you restore a server after a disaster has happened. Since there is no disaster recovery, or even database restores with SQL Azure, then you don’t need to be worried about replacing permissions. You care about this system view mostly for one thing: troubleshooting. It helps to show people that the users have not been altered recently. And you can only do that if you are capturing these details on a regular basis. It is a good idea to track the users on a regular basis, even in SQL Azure, so when someone comes to you and says “what changed”, you will be able to quickly verify if something has changed.

An easy way to do this would be to run the following query:

SELECT *
FROM sys.sysusers
WHERE DATEDIFF (dd, updatedate, GETDATE()) < 1

That query will quickly tell you if any user has been altered in the past day. If you wanted to track specific permissions for the users you could also gather the details daily with the following (courtesy of Tim Chapman):

SELECT dp.class_desc, dp.permission_name, dp.state_desc,
ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id

That query takes advantage of the sys.database_principals system and the sys.database_permissions views. Remember I told you all the goodness you could find in the sys.all_objects view yesterday? Yeah, it is nice to browse through there every now and then to see what is available, and what existing code you already have may work against SQL Azure.

Tomorrow we will take a look at some information regarding the databases on your SQL Azure server instance.