Grant a login of Sql server authentication the same permission of a Windows group login with Windows authentication?

Question

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]

Answer 1

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.

Answer 2

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.