September 28, 2018 at 6:16 am
My second DBA question in two days. I'll need a holiday if this keeps up.
I have a user U1, who is a member of AD group ADG1.
Post new topic
ADG1 has a database login and associated database users, giving members of this AD group read access to various databases on the instance.
I want to give U1 dbowner rights in one particular database (this is not a Prod environment).
For the moment, I've done this by actually defining him as the database owner.
I cannot add him to the dbowner role, because he does not exist, individually, as a user.
My question: is there another way of giving him the access he needs without creating his own separate login and user creds?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 28, 2018 at 11:21 am
You could create a separate AD group ADG2. Add ADG2 Group as a member of ADG1. Add ADG2 to SQL server, grant it dbo on the select database. That way, ADG2 has all of the permissions of ADG1 + it has dbo on the select database. Add U1 to ADG2 group. Then when you get additional users needing the same permissions as U1, you just drop them in ADG2.
Members of just ADG1 would not have DBO on the select database.
OR you could just script out all of the permissions and roles that ADG1 has, grant them all to ADG2 + the additional permissions. Put U1 in ADG2. That way you don't have nested permissions.
September 28, 2018 at 11:34 am
Travis Halsell - Friday, September 28, 2018 11:21 AMYou could create a separate AD group ADG2. Add ADG2 Group as a member of ADG1. Add ADG2 to SQL server, grant it dbo on the select database. That way, ADG2 has all of the permissions of ADG1 + it has dbo on the select database. Add U1 to ADG2 group. Then when you get additional users needing the same permissions as U1, you just drop them in ADG2.Members of just ADG1 would not have DBO on the select database.
OR you could just script out all of the permissions and roles that ADG1 has, grant them all to ADG2 + the additional permissions. Put U1 in ADG2. That way you don't have nested permissions.
Thanks very much.
That makes sense and it may come to that, but at the moment, this is very much a one-off, for a single user, and a separate AD group would be overkill.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 28, 2018 at 11:38 am
Phil Parkin - Friday, September 28, 2018 11:34 AMTravis Halsell - Friday, September 28, 2018 11:21 AMYou could create a separate AD group ADG2. Add ADG2 Group as a member of ADG1. Add ADG2 to SQL server, grant it dbo on the select database. That way, ADG2 has all of the permissions of ADG1 + it has dbo on the select database. Add U1 to ADG2 group. Then when you get additional users needing the same permissions as U1, you just drop them in ADG2.Members of just ADG1 would not have DBO on the select database.
OR you could just script out all of the permissions and roles that ADG1 has, grant them all to ADG2 + the additional permissions. Put U1 in ADG2. That way you don't have nested permissions.
Thanks very much.
That makes sense and it may come to that, but at the moment, this is very much a one-off, for a single user, and a separate AD group would be overkill.
Yep, I get that. And personally, I will create an AD group over mapping the individual user in and granting individual permissions. It gets messy real quick.
If all of the users were mapped in, you could deny access to them specifically (since deny trumps all) and grant to the AD group while omitting deny on the user you intend to give the permissions to.
September 28, 2018 at 2:52 pm
Phil Parkin - Friday, September 28, 2018 6:16 AMI want to give U1 dbowner rights in one particular database (this is not a Prod environment).
I cannot add him to the dbowner role, because he does not exist, individually, as a user.
My question: is there another way of giving him the access he needs without creating his own separate login and user creds?
Even though the Management Studio interface yells at you if you try it, I think running the sp_addrolemember command still works. It successfully runs and then I can see the AD group member in the database role afterwards.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql?view=sql-server-2017
October 2, 2018 at 12:17 pm
Chris Harshman - Friday, September 28, 2018 2:52 PMPhil Parkin - Friday, September 28, 2018 6:16 AMI want to give U1 dbowner rights in one particular database (this is not a Prod environment).
I cannot add him to the dbowner role, because he does not exist, individually, as a user.
My question: is there another way of giving him the access he needs without creating his own separate login and user creds?Even though the Management Studio interface yells at you if you try it, I think running the sp_addrolemember command still works. It successfully runs and then I can see the AD group member in the database role afterwards.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql?view=sql-server-2017
Just saw this - and it is the same kind of issue you would run into if you wanted to grant permissions on a read-only secondary but did not want to grant the user access to the primary.
If the account is a windows account - you can create the user in the database without tying the user to a login. Then you can add that user to any defined roles and/or grant specific permissions.
If the account is a SQL login - you can also create the user without tying that user to a login but it would not work because you have not assigned the SID. For these situations we need to create the login (capture the SID), create the user - grant permissions - in the primary database. Then - we move to the secondary and create the login with the same SID. Once completed you can delete or inactivate the login on the primary.
In the specified database:
Create User [domain\user] With default_schema = dbo; --or whatever default schema you need
Alter Role db_owner Add Member [domain\user];
For a read-only secondary, I generally do this:
Create User [domain\user] With default_schema = dbo;
Alter Role db_datareader Add Member [domain\user];
Grant Execute On schema::dbo To [domain\user];
Grant View Definition On schema::dbo To [domain\user];
When the user logs into SQL Server - they are authenticated by membership in the group, and the domain user at the database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 2, 2018 at 12:39 pm
Jeffrey Williams 3188 - Tuesday, October 2, 2018 12:17 PMChris Harshman - Friday, September 28, 2018 2:52 PMPhil Parkin - Friday, September 28, 2018 6:16 AMI want to give U1 dbowner rights in one particular database (this is not a Prod environment).
I cannot add him to the dbowner role, because he does not exist, individually, as a user.
My question: is there another way of giving him the access he needs without creating his own separate login and user creds?Even though the Management Studio interface yells at you if you try it, I think running the sp_addrolemember command still works. It successfully runs and then I can see the AD group member in the database role afterwards.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql?view=sql-server-2017Just saw this - and it is the same kind of issue you would run into if you wanted to grant permissions on a read-only secondary but did not want to grant the user access to the primary.
If the account is a windows account - you can create the user in the database without tying the user to a login. Then you can add that user to any defined roles and/or grant specific permissions.
If the account is a SQL login - you can also create the user without tying that user to a login but it would not work because you have not assigned the SID. For these situations we need to create the login (capture the SID), create the user - grant permissions - in the primary database. Then - we move to the secondary and create the login with the same SID. Once completed you can delete or inactivate the login on the primary.
In the specified database:
Create User [domain\user] With default_schema = dbo; --or whatever default schema you need
Alter Role db_owner Add Member [domain\user];For a read-only secondary, I generally do this:
Create User [domain\user] With default_schema = dbo;
Alter Role db_datareader Add Member [domain\user];
Grant Execute On schema::dbo To [domain\user];
Grant View Definition On schema::dbo To [domain\user];When the user logs into SQL Server - they are authenticated by membership in the group, and the domain user at the database.
Perfect. Just what I wanted, thanks.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply