Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It's usual in an organization I work in that I need to request a database creation to the DBA group (I am a developer). As such, they will also create a user for me to use and a database dedicated to my application.

When they do so, they ask which types of permissions and I answer.

When the work is done, they request that I test that everything is fine as to give my approval on the configuration. However, at this point, the database is completely empty. I can easily test for connectivity and access permissions, but how should I test my own read/write/execute permissions into a specific database, if it´s just been created and it is completely empty, using a non-admin user?

If the database wasn't empty I would try selecting data from a table and updating it.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

The question could use some clarification. SQL Server security is the same whether or not the table is empty. You have server roles, database roles, and explicit permissions. You are asking 'how can I test execute permissions on a database that has no objects to execute'.

Well the answer is you have nothing to test if the DB is empty, that should be obvious. create objects then test it, and then remove the objects. Test it on a dev box. If you're worried that you'll forget to remove all objects, backup the DB, then do your tests, and restore it. If you like, restore the DB as a different name, do all of your testing on there, then drop the newly created test DB. I'm not sure what you're asking.

Final answer remains that you cannot test permissions on objects if you have no objects to test.

I will provide a script or two at the end to help you with these steps.

Step 1: Look for users with SA rights.

What you would look for first would be SA equivelent users. Any SA user will have full access to every single object, and if they don't, they can grant it to themselves.

Step 2: Look for users with the DB_Owner role Users granted the DB_Owner role will be able to perform any task in the database. You could DENY them access to certain features but they can just GRANT themselves access to it again.

Step 3: Audit what users are in the database You will want to look at the 'security' settings of each user in the database. This could get very in depth as you can look for indvl permissions on each object and this would be required if you were taking over a database. In your case, there are no objects and you get to create them yourself, thus we don't have any object level permissions to go through. You will want to check what roles each user is in. DataReader will be able to read anything in the DB unless explicitly blocked. DataWriter will be able to write anything. Execute role lets them execute sprocs.

Step 4: Continue to monitor after objects are created Now comes the hard part. What happens when you go live and a sysadmin grants themselves rights to the machine/db? What if you have the option to have start up stored procs execute? If you're on 2008R2 and below, the NTAUTHORITY user can always be used as a backdoor. This is where real security auditing and security policies are needed but luckily it's out of scope of this question.

Check out: http://www.mssqltips.com/sqlservertip/1881/sql-server-security-audit-report/

His script will cover almost everything you're looking for.

Also this one is very good too, but test it out first: http://gallery.technet.microsoft.com/scriptcenter/SQL-SERVER-SECURITY-AUDIT-eb5b0fd5/view/Discussions

If you want to do some research on your own, search for "SQL Server Auditing" or "Object Level Auditing SQL Server".

Does this info help? Do you have any follow up questions?

Follow up edit to address permissions under the current user context:

In that case you'll want to use this:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');


USE DBName;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

The first one will tell you your permissions on the server, the 2nd one will tell you your permissions on the database.

Click here for more easy to read info.

share|improve this answer
    
That makes sense. Still, my user should have different permissions or roles set independently of whichever objects exist in the DB. (So that, when created, I will have access to them.) How can I verify them if that user is the only access I have? –  Alpha Nov 29 '12 at 18:45
    
Also, if you need any clarification, feel free to let me know what information I am missing and I'll gladly add it up. –  Alpha Nov 29 '12 at 18:50
    
Ah ok, i see what you mean. In this case what you want to do is perform an audit. Updating my original answer to provide more info. –  Ali Razeghi Nov 29 '12 at 18:51
    
Updated! Take a look and let us know if you need more info/help. –  Ali Razeghi Nov 29 '12 at 19:05
1  
Just updated answer to include your requirements. Check out: SELECT * FROM fn_my_permissions (NULL, 'DATABASE'); –  Ali Razeghi Nov 29 '12 at 19:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.