May 25, 2006 at 2:25 pm
Firstly, I would like to say this site is an amazing resource of information and I have been searching through discussions and articles all day in an attempt to find the answer to my question. I am now better informed, but unfortunately no closer to a solution.
Let me describe my issue. I have a new Windows 2003 Server running MSSQL 2005; all Windows and SQL patches applied (sp1, etc..). I have run the Security Configuration Wizard and enabled the built-in Windows firewall, though they have been configured to allow SQL access incoming from remote sources (same subnet, not beyond).
I am able to use SSMS to administer my databases, create users, assign roles, run queries etc... No problems there. My MSSSQL server is running in mixed mode authentication and I can log into SSMS with either my Windows admin account or my sa SQL user.
Ok, this is where life gets interesting. Now I will leave, for the time being, that I am attempting to access this MSSQL server from an IIS server in the same subnet, and we will stick to local symptoms (since I feel it is a configuration issue with MSSQL server itself). So, I have created a new database called 'NewDB'. I have created a login called 'NewUser1' that is a SQL Authentication login. I also specifiy that 'NewUser1' has a default database of 'NewDB' and is UserMapped to 'NewUser1' for database 'NewDB' with schema 'dbo'. The Status of the login 'NewUser1' includes Grant access to the Database engine and Login enabled.
In the user settings for the 'NewDB' database, this user is given the roles of db_datareader, and db_datawriter. Now, when I attempt to execute the following T-SQL script:
USE NewDB
GO
EXECUTE AS 'NewUser1' SELECT * FROM dbo.DemoTable
GO
(Where DemoTable is an existing table in NewDB) I get the following error:
Msg 916, Level 14, State 1, Line 1
The server principal "NewUser1" is not able to access the database "NewDB" under the current security context.
Ok, so I have been searching for how to grant access to this user for reading and writing data to tables of one database, but I have been stumped. Admittedly I am more familiar with MySQL databases, but I am pretty sure I have covered all my bases. Any gentle nudges in the right direction would be appreciated. I am sure it is something on the role or Schema level that is thwarting me, but damned if I can't find it.
Andrew
May 26, 2006 at 8:14 am
I think you will also need to give the user IMPERSONATE permission although it looks like you are connecting as NewUser1. If that is the case you would Execute As Caller. Caller doesn't require Impersonate permission.
From BOL:
Permissions
To specify EXECUTE AS on a login, the caller must have IMPERSONATE permissions on the specified login name. To specify EXECUTE AS on a database user, the caller must have IMPERSONATE permissions on the specified user name. When EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
May 26, 2006 at 8:56 am
Hmmm... Damn. Maybe I am not using the correct way to test my setup. I don't really want to give IMPERSONATE permissions to a user if I don't have to. Is there a way to test if a user can access the mssql database locally (maybe a command line sql client)? My problem is that no matter what I do, the only users that can log in to access my mssql server are users that are given the server role of sysadmin, and that is not what I want to do.
I am trying to specify a SQL user that can read and write a database from another server. Simple stuff, or so I thought. I am trying to troubleshoot, but I can't find a good way to test it locally. Remotely I get the following login errors:
Login failed for user 'NewUser1'.[CLIENT: 192.168.1.20]
Error: 18456, Severity: 14, State:12.
This error is pretty generic and searches on it shows lots of issues that don't seem to be related to mine. I get the same error in the logs with local connections. I figure I should test locally first, then worry about external connections.
Andrew
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply