Home Forums SQL Server 2008 Security (SS2K8) windows user is created and unable to connect to sqlserver RE: windows user is created and unable to connect to sqlserver

  • new users are at least a three step process:

    create the user in Active directory (you said that was done)

    create a login for the user in SQL Server(you said that was done)

    Create a user in the database to match the login(missing?)

    Assign the appropriate permissions/role so the user can access data(missing)

    --creat the login for the Active Directory User

    CREATE LOGIN [mydomain\NewUse] FROM WINDOWS;

    --switch to the right database: if more than one database, repeat for each one

    USE SandBox;

    GO

    --Create the user to match the login

    CREATE USER [mydomain\NewUser] FROM LOGIN [mydomain\NewUser]

    --assign permissions

    --put the user in the correct permissions role

    exec sp_addrolemember 'RoleAlreadyCreated', 'mydomain\NewUser'

    --an example in case you don't have the role with the right permissions yet:

    CREATE ROLE [AlmostOwners]

    EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'

    EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'

    EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'

    --can the users EXECUTE procedures? comment out if false

    GRANT EXECUTE TO [AlmostOwners]

    exec sp_addrolemember 'AlmostOwners', ' mydomain\NewUser'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!