December 20, 2011 at 9:10 am
First off, I'm an Oracle DBA and have not spent a lot of time with MSSQL, particularly with 2008 and it's GUI.
I created a database for a new application being tested, created the database while I was logged in to SSMS with my domain admin windows user authentication. As per the applications install instructions, created a database user called "Admin" and gave it permissions to the database. I did this the same way I did previously with earlier versions of MSSQL, created a login, then added a user to the database and picked that login. The trouble is I cannot see any of the tables in the new database from the "Admin" user account. I have given every permission I can find to the Admin login/user, the new database is the default for the Admin user, default schema is dbo, all the tables are dbo.tablename etc..
The application is not letting the Admin user log in, complaining about permissions, when I connect as Admin with SSMS I cannot see any tables in this database.
I obviously missed a step here somewhere, but can't see it for looking.. I tried changing ownership of the database to the Admin user, but it complains about that because there are rights assigned to that owner, and I can't even drop the user because the user owns a schema in the database. If the user owns the schema, why can't it see anything?
December 20, 2011 at 9:13 am
What database roles did you grant? Windows login or SQL login?
What are the errors you're getting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 9:21 am
You might have missed on some steps in Installation Manual (your organization specific). Please crosscheck it. If possible, redo it from scratch (if it doesn’t take much time).
OR
Provide the information what Gail has already requested. We will help you from that point onwards.
December 20, 2011 at 9:23 am
Under the database/Admin/properties, the user has every single role membership checked off, and all of the owned schemas checked except guest, which is not allowed. (I reverted to shotgun effect)
Under the login/Admin/properties, public is checked off for server roles, all of the role memberships are checked off for user mapping on the logins default database (the database I cant see anything in), and the user is set for SQL Server authentication with password.
December 20, 2011 at 9:25 am
And the error messages?
btw, if that user just needs to read tables, all that should be checked under database roles is db_datareader. Nothing more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 9:26 am
Dev (12/20/2011)
You might have missed on some steps in Installation Manual (your organization specific). Please crosscheck it. If possible, redo it from scratch (if it doesn’t take much time).
Reinstall from scratch to fix authentication problems? Isn't that well into the 'overkill' category? Is there a single installation problem you can think of that causes login failures?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 9:31 am
I connected as new user with SSMS, and nothing shows up under the tables tab, but when I right click to create a table, I get sql server error 229 "the select permission was denied on the object 'schemas',database 'mssqlsystemresource',schema 'sys'
It's obviously somehow equivalent to creating something in the Sys schema in an Oracle DB, which is a no-no in Oracle, but what Oracle call's a schema MSSQL treats as a database - still unsure exactly what schema means in the MSSQL world..
December 20, 2011 at 9:33 am
When you granted rights to the user, did you perhaps check the 'deny' rights too?
Bex
December 20, 2011 at 9:34 am
GilaMonster (12/20/2011)
Dev (12/20/2011)
You might have missed on some steps in Installation Manual (your organization specific). Please crosscheck it. If possible, redo it from scratch (if it doesn’t take much time).
LOL... You got me wrong. When I said 'Installation Manual (your organization specific)' I mean steps to re-create DB and then user access management. It's called Application Installation. I was not asking for SQL Server re-installation.
I asked it because we can’t guess here, what permission are required for their application. They might have created their own roles (may be proprietary). I also added ‘if it doesn’t take much time’ as a precaution because it would be overkill.
December 20, 2011 at 9:37 am
Glenstr (12/20/2011)
...It's obviously somehow equivalent to creating something in the Sys schema in an Oracle DB, which is a no-no in Oracle, but what Oracle call's a schema MSSQL treats as a database - still unsure exactly what schema means in the MSSQL world..
I can feel your pain. Don't worry Bro... It's not hard. 😉
December 20, 2011 at 9:39 am
You checked every single role for the user? So, you gave them (for example) db_datareader access and db_denydatareader access. A DENY (almost) always overrides a GRANT.
December 20, 2011 at 9:40 am
the application instructions are vague (surprise surprise) and read as:
Setting up MS SQL Server to be used for EMCAT3 – ENTERPRISE
1.Create a database on the server named: emcatdb.
2.Create a user named Admin with an appropriate password.
3.Make sure this user has full privileges for the database emcatdb and needed privileges for SQL Server.
Then I ran a script they had for creating the app tables, but I ran it under my user authenticated login, which probably should have been done as the user - however I thought that granting the user the proper permissions etc. would suffice.
From what it looks like, I have basically given the user "system" access (in the oracle world) so I don't know whats wrong here.
December 20, 2011 at 9:41 am
Full privileges would be the db_owner database role.
December 20, 2011 at 9:52 am
Ok I think I am making some headway, I unchecked the db_denydatareader and db_denydatawriter in the "Admin" login properties and just left db_owner and public checked. I was confusing all those rights with the "grant" option when editing an Oracle user account, which gives that user the power to deny or enable the same grant to others. Now at least I can get to a create table window, but the tables I created still don't show up.
Also in the database/Admin<user>/properties the db_deny.. options are still checked and not letting me uncheck them...
December 20, 2011 at 9:53 am
Robert is correct. Just grant the db_owner role. That gives you all the privileges in the database.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply