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.