|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stupid permissions questionI restored a SQL Server 2005 database from a backup made on another server, and I can't figure out how to create a user that has access to it without giving him the sysadmin role. The database is in schema "Bob". I created a user named Bob. I created a login named Bob. Database user Bob owns the "Bob" schema. Bob can connect to the server, but he can't use the database. > USE myDb; under the current security context.> The server principal "bob" is not able to access the database "mydb" If I give the login Bob the server role of Sysadmin, he can access the database, but his default schema setting is ignored. > SELECT * FROM employees; Nothing else I've tried allows him to use the database at all.> Invalid object name 'employees'. > SELECT * FROM bob.employees; > (this works fine) What do I need to do to be able to log in as Bob and run "SELECT * FROM employees" successfully? Thanks! Jesse
Did you run CREATE USER bob FOR LOGIN bob;? Did you grant VIEW DEFINITION permission to the user? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_6f78.asp Show quote "Jesse" <nospam@nospam.com> wrote in message news:%23SuJItesGHA.4752@TK2MSFTNGP02.phx.gbl... > Here's an easy question I can't seem to answer: > > I restored a SQL Server 2005 database from a backup made on another > server, and I can't figure out how to create a user that has access to it > without giving him the sysadmin role. > > The database is in schema "Bob". > I created a user named Bob. > I created a login named Bob. > Database user Bob owns the "Bob" schema. > > Bob can connect to the server, but he can't use the database. > > > USE myDb; > > > The server principal "bob" is not able to access the database "mydb" > under the current security context. > > If I give the login Bob the server role of Sysadmin, he can access the > database, but his default schema setting is ignored. > > > SELECT * FROM employees; > > > Invalid object name 'employees'. > > > SELECT * FROM bob.employees; > > > (this works fine) > > > Nothing else I've tried allows him to use the database at all. > > What do I need to do to be able to log in as Bob and run "SELECT * FROM > employees" successfully? > > Thanks! > Thanks, Uri -- granting VIEW DEFINITION allowed me to do "USE mydb".
So only one problem left: when I log in as user bob, I still have to do "SELECT * FROM bob.employees" instead of just "SELECT * FROM employees". ALTER USER bob WITH DEFAULT_SCHEMA = bob completed successfully, but didn't solve the problem. Any more ideas? Thanks! (I can't run CREATE USER bob FOR LOGIN bob because bob already exists. I can't drop & recreate bob because he owns the schema that the whole db is in. Maybe I can create a new user, transfer ownership of everything over to the new guy, then drop & recreate bob, and move everything back?) Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:egaz5HfsGHA.4264@TK2MSFTNGP04.phx.gbl... > Jesse > Did you run CREATE USER bob FOR LOGIN bob;? > Did you grant VIEW DEFINITION permission to the user? One extra bit of info: if I log in as bob and create a table -- create table
foo (bar int); -- it shows up as dbo.foo, not bob.foo. Jesse
I think it is because Bob is a member of db_owner fixe database role , try to remove him from this role and issue CREATE TABLE t (c1 INT) , it should be shown as Bob.t , isn't it? Show quote "Jesse" <nospam@nospam.com> wrote in message news:%23uUxxrfsGHA.4324@TK2MSFTNGP05.phx.gbl... > One extra bit of info: if I log in as bob and create a table -- create > table foo (bar int); -- it shows up as dbo.foo, not bob.foo. > > Hi Uri,
Turns out the problem was that the login bob was mapped to the user bob in Master, not in mydb. Using SQL Server management studio, I was unable to fix this -- I got an error saying that the user already existed in mydb when I tried. (Maybe you can only set that mapping when you create a user? I couldn't find a way to do it in transact-sql either.) Anyway, I created a new login and a new user, and set it as the owner of schema Bob, and now everything works as expected. Thanks for the help. Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:OqJVU%23fsGHA.3368@TK2MSFTNGP02.phx.gbl... > Jesse > I think it is because Bob is a member of db_owner fixe database role , > try to remove him from this role and issue > CREATE TABLE t (c1 INT) , it should be shown as Bob.t , isn't it? > > > "Jesse" <nospam@nospam.com> wrote in message > news:%23uUxxrfsGHA.4324@TK2MSFTNGP05.phx.gbl... >> One extra bit of info: if I log in as bob and create a table -- create >> table foo (bar int); -- it shows up as dbo.foo, not bob.foo. >> >> > > Jesse wrote:
> Hi Uri, This happened because you restored a backup from a different server, a > > Turns out the problem was that the login bob was mapped to the user bob in > Master, not in mydb. Using SQL Server management studio, I was unable to fix > this -- I got an error saying that the user already existed in mydb when I > tried. (Maybe you can only set that mapping when you create a user? I > couldn't find a way to do it in transact-sql either.) > > Anyway, I created a new login and a new user, and set it as the owner of > schema Bob, and now everything works as expected. > > Thanks for the help. > server on which Bob existed as a user and had access to that database. You brought the database over, but not the users. When a user is created, they are assigned a "sid", an internal ID number. User permissions are stored using that sid, not the username. Example: Server A: User Bob created, sid "0x12345" Database permissions granted to sid "0x12345" User Bob (0x12345) has permissions assigned to sid "0x12345" Server B: User Bob created, sid "0x65432" Database restored, contains permissions for sid "0x12345" User Bob (0x65432) has no permissions See here for details: http://support.microsoft.com/kb/274188/ Thanks, Tracy -- that makes sense.
Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:ej5vjbksGHA.4956@TK2MSFTNGP02.phx.gbl... > This happened because you restored a backup from a different server, a > server on which Bob existed as a user and had access to that database. You > brought the database over, but not the users. When a user is created, > they are assigned a "sid", an internal ID number. User permissions are > stored using that sid, not the username. > > Example: > Server A: > User Bob created, sid "0x12345" > Database permissions granted to sid "0x12345" > User Bob (0x12345) has permissions assigned to sid "0x12345" > > Server B: > User Bob created, sid "0x65432" > Database restored, contains permissions for sid "0x12345" > User Bob (0x65432) has no permissions > > See here for details: > http://support.microsoft.com/kb/274188/ > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Jesse,
Have you tried detaching and reattaching the database? When you attach, you have a chance to choose an owner.  |
|||||||||||||||||||||||
Other interesting topics