May 21, 2012 at 10:00 am
I am running in an environment with multiple domain. I am looking for a way to automate scripting my database users so they can be backed up. My issue is that I can script the user to get something like this:
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyUser') CREATE USER [MyUser] FOR LOGIN [DomainA\MyUser] WITH DEFAULT_SCHEMA=[MyUser];
The issue is that I cannot determine a way to identify DomainA dynamically. What happens if MyUser is in DomainB? Is there a place to look for that? I've tried sys.database_principals and sysusers, but no luck. Any ideas?
The script needs to support SQL 2008 and SQL 2008 R2.
Thanks in advance for your help!
May 21, 2012 at 10:45 am
Domain accounts are linked to logins rather than users.
The domain name should be stored in sys.server_principals (at the start of the name field), you can link to sys.database_principals via the SID field.
Think that will give you the info you need, not entirely sure what you're trying to do here though.
May 21, 2012 at 12:13 pm
Thank you for your reply.
The issue is that my users access the server through an AD group Login. Then, to access the system, the application logs them in to an individual database. This is where their database user would exist.
For example:
AD Group: DomainA\MSSQL_MyApp
AD Group Members: DomainA\User1, DomainB\User2
Server Login: DomainA\MSSQL_MyApp
Database User: DomainA\User1
Database User: DomainB\User2
When I try to automate the scripting of the database user, how do I resolve that User1 and User2 are in different domains?
With this structure, I'd have to dig through Database_principals to server_principals to get the AD group to find the AD group members whose short names matches my database user name. That seems like a long walk for a little information, and fraught with stumbling blocks. I was hoping there would be another way to resolve the user domain.
May 22, 2012 at 1:34 am
You can grant permissions in SQL Server to AD Groups as well as AD Users, in fact i'd use this approach wherever possible, for AD security use local groups to provide the access to the resource, in this case that is the database, add a universal group to the local group and then the global group for the 2x domain's to the universal group so it goes like this:
GG > UG > LG
May 22, 2012 at 2:40 am
I think you lose that information when you use a group to login rather than the domain user account.
Far as I can tell, the only way with this configuration would be the way you've described - find the members of the group in AD, and compare from there.
I'd also second the suggestion by SQLDBA360 to assign permissions to groups rather than individual user accounts, although it sounds like that might not be an option?
Thanks
May 22, 2012 at 7:12 am
Thanks for the responses, I really appreciate it.
In this case we do need to maintain security at the database level by individual user, not group. We use groups for logins to keep them more manageable, however we need to be able to assign each user in the group different db roles. The application uses db roles and db users as the security mechanism, so groups don't work in this case.
Aside from building powershell scripts to dig through the security groups are there any other ideas out there? This infrastructure is less than ideal, but the fix is months out so I'm stuck with multiple domains and applications using database users for authentication in the meantime.
May 23, 2012 at 4:36 am
Can I just clarify that your trying to script users at a database level not logins at an instant level? Do users frequently change roles within your application or is this for recovery purposes?
If its at the instance level you can generate a script for all logins using sp_help_revlogin http://support.microsoft.com/kb/918992
May 23, 2012 at 6:56 am
It is at the database level for a database user, not at the instance level for a Login. That's what has made this so difficult to resolve.
I've tried running SQL Profiler while right-clicking a database user and "Script User as Create" to a new window to see if there were any stored procs that SQL was using to gather the information, but I had no luck. Clearly SSMS has the ability to resolve the domain of the user because the script that is produced clearly ties back to a login:
CREATE USER [UserA] FOR LOGIN [DomainA\UserA] WITH DEFAULT_SCHEMA=[dbo]
In my example, DomainA\UserA does not explicitly exist as a Login on the instance. DomainA\UserA is a member of a Windows AD group. Somewhere internally SSMS or the DB Engine itself must be mapping to the user via SID or something of that nature.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply