Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Update: I am trying to make it easier for the login to have the permissions on multiple databases.

I already have a Windows group login with Windows authentication. The Windows users in the group can access multiple databases on a server.

Now I need to create an Sql server login and I want it have the same permission of the Windows group login (so it will have the permissions on mulitple database). Is it possible to do it without grant all the permissions to the SQL server separately?

I tried to create a credential for the Windows group login and add the credential to the SQL login but it doesn't work.

create credential WinGroupLogin with identity = 'MyServer\WinGroupLogin'
ALTER LOGIN [sql_login] ADD CREDENTIAL [WinGroupLogin]
share|improve this question
add comment

2 Answers

At the database level, you could simply add both to the same database role. Of course, when you create the role, you'll have to grant it all the same permissions as the existing group, but you only have to do that once.

At the server level, to do that without re-applying all of the permissions (which you could probably script without a whole lot of effort - you can get some ideas here), you'll have to wait for SQL Server 2012, unless you can use a fixed server role.

share|improve this answer
    
The login needs to inherit permissions on multiple databases. I'm trying to not create database users on these DBs too. –  u23432534 Oct 24 '13 at 21:49
    
So you want a server-level login to inherit database-level permissions on multiple databases without creating database users? Well, I fold. –  Aaron Bertrand Oct 24 '13 at 21:56
    
I've been doing this by creating Windows group login so I can add the Windows users to the group. I thought the credential can make Sql login inherit the permissions of the group. –  u23432534 Oct 24 '13 at 21:59
    
I don't know of a way, but I am asking a friend who should know one way or another. –  Aaron Bertrand Oct 24 '13 at 22:04
    
You shouldn't be granting privileges to users directly IMHO. If you granted privileges to a role and added whoever needs those privileges to the appropriate roles you would solve your problem easily. I faced the same problem in the past and learned my lesson the hard way. It could look like overkill at the beginning, but saves a lot of work in the long run. Login means identity, SQL/Windows means authentication, role means authorization. –  spaghettidba Oct 24 '13 at 22:23
add comment

There's no way to do this automatically. As they are separate logins the permissions must be applied separately to each one granting them rights into the specific databases that they need rights to.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.