locked
Help please with SQL Server 2008 permissions RRS feed

  • Question

  • HI

    Today I migrated a database from SQL Server 2005 to SQL Server 2008 RC2 using the backup/restore method which went fine and did not generate any errors. 

    I can log into sql server 2008 through the server management studio using NT Authentication under the Administrator user and view the tables, run select statements, etc, the Administrator user is the owner of the database that I migrated.

    The problem is, when I log into the sql server using NT Authentication under a different username I cannot run select statements or view the tables, views, stored procedures or anything. 

    For example, whenever I run a select statement I get the error

    "The SELECT permission was denied on the object 'AAPUsers', database 'PINT', schema 'dbo'."

    When I try to expand the tables tree I get the error

    "The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)"

    I have tried giving my other user full permission to everything I can find but the problem continues.

    I have found that if I create an SQL server user and login in with that user and give them db_datareader and db_datawriter access to the database then it all works fine, its just when I use NT Authentication.

    I have tried giving the domain user (eg domain/user) the above permissions but it didn’t fix the problem. If I make my domain user the owner of the database then everything works, as one would expect.

    I also created a test database on 2005 with 1 table and migrated that to 2008 using the same backup/restore method and once I have given my domain user db_datareader and db_datawriter access it works fine so its something to do with that particular database but I can figure it out.

    The problem seems to be around NT Authenticated users, I'm sure it just something simple that I need to do.  

    I would very much appreciate some help with this as I've tried everything I can think of and am out of options.


    Thursday, June 23, 2011 4:05 AM

All replies

  • Hi,

    Did you migrate logins also from 2000 to sql 2005?

    Check for orphan users for the database also using.

    sp_change_users_login 'report'

     

    it could be that the user is not mapped to the correct login

     

    Regards,

    sid

    Thursday, June 23, 2011 7:01 AM
  • So in SQL Server 2005 when log in with that user it was NO problems right?... You need to grant CONNECT permission to the server and create the same permission as it has on the old server

    GRANT EXEC ON some_sp TO nisse -- Grant permission on a single procedure.

    GRANT EXEC ON SCHEMA::dbo TO kalle -- Grant perpmission on all procedures in

                                          the dbo schema

    GRANT EXEC TO putte -- Grant EXEC permission all procedures in the database.

     

     

    To grant all "normal" permissions on a table

     

       GRANT SELECT, UPDATE, DELETE, INSERT on pec TO freddie

     

    To grant freddie access on tables in a schema:

     

       GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO freddie

     

    To grant him access on all tables:

     

       GRANT SELECT, UPDATE, DELETE, INSERT TO freddie

     

    SELECT 'REVOKE EXECUTE ON ' + quotename(s.name) + '.' + 

                                  quotename(o.name) + ' TO  UserJoe'

    FROM   sys.objects o

    JOIN   sys.schemas s ON o.schema_id = s.schema_id

    WHERE  o.type = 'P'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 23, 2011 8:28 AM
  • So in SQL Server 2005 when log in with that user it was NO problems right?... You need to grant CONNECT permission to the server and create the same permission as it has on the old server

    GRANT EXEC ON some_sp TO nisse -- Grant permission on a single procedure.

    GRANT EXEC ON SCHEMA::dbo TO kalle -- Grant perpmission on all procedures in

                                          the dbo schema

    GRANT EXEC TO putte -- Grant EXEC permission all procedures in the database.

     

     

    To grant all "normal" permissions on a table

     

       GRANT SELECT, UPDATE, DELETE, INSERT on pec TO freddie

     

    To grant freddie access on tables in a schema:

     

       GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO freddie

     

    To grant him access on all tables:

     

       GRANT SELECT, UPDATE, DELETE, INSERT TO freddie

     

    SELECT 'REVOKE EXECUTE ON ' + quotename(s.name) + '.' + 

                                  quotename(o.name) + ' TO  UserJoe'

    FROM   sys.objects o

    JOIN   sys.schemas s ON o.schema_id = s.schema_id

    WHERE  o.type = 'P'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 23, 2011 8:28 AM
  • Hi:

    Please try the following and let us know the results

    select

    USER

    select

    USER_ID

    ()

    select

    USER_NAME()

     

    Also check if the following works for you,

    create

    role db_tempselecter

    grant

    select to db_tempselecter

    grant

    alter to db_tempselecter

      Now add the user to this role and see if still lack permission

    -Hari K
    Thursday, June 23, 2011 1:50 PM
  • Since your error message said "The SELECT permission was denied ", I think there is a permission denial somewhere that is overriding all of your grants. The following statement will tell you what explicit denials are in the database.

    SELECT state_desc, permission_name, 'ON', Objts.name, 'TO', Princ.name
    FROM sys.database_permissions AS Perms
    JOIN sys.database_principals AS Princ
    ON Perms.grantee_principal_id = Princ.principal_id
    JOIN sys.objects AS Objts
    ON Objts.object_id = Perms.major_id
    WHERE state_desc = 'DENY' 

    This won't pick up any denials caused by membership in the db_denydatareader role. 


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, June 23, 2011 3:59 PM
  • HI Thank you for all of your comments, unfortunately the problem still persists. Here is the information each of you asked for.

    Sid: I did migrate the user logins from the old server to the new one, I ran sp_change_users_login 'report' and nothing was return so I assume that there are no orphaned logins

    Uri Dimant: All of the users in the new database already have connect, select, update, etc permissions, i even created a new user and gave it full access to everything but I still received permission denied when executing a select or trying to expand the tables tree.

    Hari Kuttappan: I ran the select query and got back three results, dbo 1 dbo. I also added the db_tempselector, added the table Billing as a securable with select permission and assigned a user to that role but I still received an access denied message when logged in as that user and executing select * from Billing.  I then created a sql user and assigned that user to the db_tempselecter role and I am able to execute select queries against the billing table without a problem.

    Rick Byham: I ran your query and returned back the following result, however this is the same on the old server.

    DENY DELETE ON ProspectStatus TO e_Prospects
    DENY DELETE ON ProspectSources TO e_Prospects

    The old database on the 2005 sql server works fine, its just the new one that I'm having this issue with. If I add a new NT Authenticated user (domain user) and give full access it doesn't work, however if I create an SQL server user then it works fine for that user. I just can't get it to work with NT authenticated users, i.e. domain users. So I think it has something to do with NT Authenticated users since sql server users work fine.

    When I first restored the database on the new server I couldn’t connect to it because of the firewall, I had to open port 1433. Do you think it could be a network related issue? However I can log into the server, I just keep getting permission denied whenever I try to do something.


    Thursday, June 23, 2011 9:51 PM
  • Update:

    I restored the database to sql server 2005 on a different machine and it all worked fine, I could view all of the tables and run select/update statements without a problem.  I then restored the database to sql server 2008 on my development machine and again I had the same permission issues.  So it is something specific to NT authenticated users on sql server 2008. I also tried the Detach - Copy database - reattach method but again I had the same permission issues.

    I need to restore the database to sql server 2008 because thats whats our production environment is running so I really need to sort this out.

    Friday, June 24, 2011 12:06 AM
  • I don't think there is any particular reason to think the problem is version related vs. machine related. But since you have been able to attach the db on a second 2005 instance, why not install SQL Server 2008 on that instance as an upgrade. Then setup should upgrade the db to 2008. Confirm it works there. Then move the 2008 db. Sounds to me like that might be worth a try. And if it works on the upgraded computer but not the previously tried 2008 computer, then you will have two computers to compare to one another.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Friday, June 24, 2011 3:05 PM
  • Hi Rick thanks for your suggestion.  On a test machine I restored the databse in question to sql server 2005 and then upgraded the sql server to 2008 and again I couldn't access the tables, run select statements, etc because of permissions.  Before I upgraded it I made sure that I did have permission to access everything and that it worked but after upgrading the same thing happened.  I really don't understanding why this is happening as I have created and moved test databases from 2005 to 2008 without any issue, its just this one particular database.

    Whenever I try to expand tables, views, security, etc I get the same error about permission denied on schema sys.  Do you think the sys schema has something to do with it?
    Sunday, June 26, 2011 9:48 PM
  • Further Update:

     

    If I assign the sysadmin role to a domain user or a domain group that the user belongs to  then it works.  However giving the user anthing else like datareader access on the database in question does not work.

    Monday, June 27, 2011 5:36 AM
  • Hi alc4emy5t,

     

    Please use the below script to check whether this user has been added to some roles which might be deny the permissions:

     

    select 
    
    [Login Type]=
    
     case sp.type
    
     when 'u' then 'WIN'
    
     when 's' then 'SQL'
    
     when 'g' then 'GRP'
    
     end,
    
     convert(char(45),sp.name) as srvLogin, 
    
    convert(char(45),sp2.name) as srvRole,
    
     convert(char(25),dbp.name) as dbUser,
    
     convert(char(25),dbp2.name) as dbRole
    
    from 
    
    sys.server_principals as sp join
    
     sys.database_principals as dbp on sp.sid=dbp.sid join
    
     sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
    
     sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join 
    
    sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
    
     sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id
    
    

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Monday, June 27, 2011 6:13 AM
  • When you run as a user that has permission problems, what does

    SELECT USER, name, type, usage FROM sys.user_token
    SELECT SYSTEM_USER, name, type, usage FROM sys.login_token

    report?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, June 27, 2011 8:40 PM
  • HI Weilin Qiao thanks for your suggestion, I tried it but none of the users or user groups returned belonged to any roles that deny access.

    Hi Erland Sommarskog thanks for your suggestions, I ran your query and got back two result sets. 

    The result set first says dbo, name: dbo, type WINDOWS LOGIN, usage GRANT OR DENY.

    The second result set contains a list of windows names, server roles and windows groups all of which have usage GRANT OR DENY.  I don't really know what it means so I hope it gives you some information.

     

    Monday, June 27, 2011 10:21 PM
  • Hi Erland Sommarskog thanks for your suggestions, I ran your query and got back two result sets. 

    The result set first says dbo, name: dbo, type WINDOWS LOGIN, usage GRANT OR DENY.

    Do you get this result when you run as a user that get permission errors? I find that difficult to believe, since if you are dbo, you have access to everything.

    The second result set contains a list of windows names, server roles and windows groups all of which have usage GRANT OR DENY.  I don't really know what it means so I hope it gives you some information.


    While it is more likely that this is an issue on user level, than on login level, it would still be good if you post the output.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 30, 2011 9:37 PM