Showing posts with label permissioning. Show all posts
Showing posts with label permissioning. Show all posts

Friday, March 9, 2012

Permissioning problem in SQL 2005...need help please!

I'm doing some data-driven scripting and I have a generic test case database with logins, etc. in it. I'm having trouble with the SQL permissioning to the database. I'm referencing a system DSN in TestPartner. As long as I have the DSN set up to use my domain account (integrated security), I can read and write to the database all day. My domain account has the sysadmin role, but no explicit database permissions. I set up a user on the SQL Server called tpuser, and gave him the same sysadmin role. However, that user cannot read or write to the same database.

I then removed the sysadmin role and gave tpuser explicit permissions to the database in the 'user mapping' section (the preferred method). I set the default schema as dbo and selected db_owner and public under 'database role membership'. The user still can't read or write to the database. What am I doing wrong?

When you connect using the tpuser, execute the following command from your application:

select suser_sname()

If you are not getting back tpuser, then you are not logged in with those credentials. If you created a standard SQL Server login, assigned it to the sysadmin role, and then logged in with that account, it will have the authority to do anything within SQL Server.