Server Role vs Database Role

I needed a little refresher the other day on Server roles vs. Database roles in SQL Server 2000. So I created a SQL User called test and gave him no access. I then opened up a query analyzer window and logged in as my new user. I was able to login successfully. I could see master, msdb, and tempdb in the database dropdown box. I tried a quick query on msdb:

select * from sysjobs

Result: Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object ‘sysjobs’, database ‘msdb’, owner ‘dbo’.

However using the stored procedure  sp_helprotect, I started to determine what access my test user had to each database. From BOL: SP_HELPROTECT: Returns a report with information about user permissions for an object, or statement permissions, in the current database. After running this against a user against on the master and msdb you quickly realize that the default created user has a lot of access.

The default user can glean the following information (and a lot more)

  • Database file location, name, etc from sysfiles in msdb
  • User information from sysusers
  • User information from syslogins. Syslogins is interesting because if you do a SELECT * from it you get the permissions error. Inspection of the output from the sp_helprotect shows which columns in syslogins that you have access to. So just alter your select to select the fields you have access to.
  • Information about objects from the sysobjects table
  • Etc. Etc.

It feels like I am just scratching the surface. In the weeks to come I am going to set aside some time to come up with a security audit technique. Anyway, to continue on with my testing I then gave the user some fixed server roles. Tested some queries, rinse repeat, etc.

Lessons Learned (or re-learned):

  • Do not give sysadmin fixed server role out unless it is absolutely justified.
  • Default users with no access have quite a bit of access to the information about your server/database
  • Used the database roles to give access to specific databases, used the fixed server roles to give access server wide.
  • Be as granular as possible when diving out permissions
  • Use the sp_helprotect stored procedure to audit what permissions a user has. Make sure application users do not have to many permissions.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s