December 9, 2016 at 9:28 am
Hello,
i'm trying to create a login and assign permission to login so that it should be able to assign any kind of permissions on any databases on that instance.
I'm trying to minimize the access level for this user, i gave security admin but it can only alter permissions for other logins at the instance not at the database level. Is there any specific role or way to setup this ?
Please let me know.
December 9, 2016 at 9:41 am
Robin35 (12/9/2016)
Hello,i'm trying to create a login and assign permission to login so that it should be able to assign any kind of permissions on any databases on that instance.
I'm trying to minimize the access level for this user, i gave security admin but it can only alter permissions for other logins at the instance not at the database level. Is there any specific role or way to setup this ?
Please let me know.
What kind of permissions do you want this user to have on a database? Should they be able to select, insert, create, drop?
Assigning a login a Server Role doesn't neccessaryly affect their permissions on a database (most don't to be honest, sysadmin is bascially the only one).
You'd need to also assign the role db_securityadmin to the login on each database.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2016 at 9:56 am
Thom A (12/9/2016)
Robin35 (12/9/2016)
Hello,i'm trying to create a login and assign permission to login so that it should be able to assign any kind of permissions on any databases on that instance.
I'm trying to minimize the access level for this user, i gave security admin but it can only alter permissions for other logins at the instance not at the database level. Is there any specific role or way to setup this ?
Please let me know.
What kind of permissions do you want this user to have on a database? Should they be able to select, insert, create, drop?
Assigning a login a Server Role doesn't neccessaryly affect their permissions on a database (most don't to be honest, sysadmin is bascially the only one).
You'd need to also assign the role db_securityadmin to the login on each database.
I think i confused you. We have a project to give permissions to users on databases when there is a release or something and we would like to automate the database permission and we have setup an applicaiton to automate this task. When a database request comes, the framework should search for the login on that instance and assign a DBO on particular database (depending on the request). So i'm created a login and using this login application connects to database and assign permissions.
I'm not sure what permissions should i assign to this login instead of SA. Please let me know if you have any questions.
December 9, 2016 at 10:00 am
Correct me if I'm wrong, but are you instead saying that this particular login should always be assigned specific permissions whenever you create any and all databases on an instance, and these permissions should be assigned at the point of creation?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2016 at 10:03 am
Thom A (12/9/2016)
Correct me if I'm wrong, but are you instead saying that this particular login should always be assigned specific permissions whenever you create any and all databases on an instance, and these permissions should be assigned at the point of creation?
This particular login should have the ability to GRANT access to other logins or users on all the databases.
December 9, 2016 at 10:17 am
Robin35 (12/9/2016)
Thom A (12/9/2016)
Correct me if I'm wrong, but are you instead saying that this particular login should always be assigned specific permissions whenever you create any and all databases on an instance, and these permissions should be assigned at the point of creation?This particular login should have the ability to GRANT access to other logins or users on all the databases.
Ok, so as I said, a login with the server role Securityadmin doesn't give any rights at a database level. You'd need to give it a user on the database, and then the db_securityadmin role on that database. They then have the ability the GRANT (or REVOKE/DENY) permissions on the database (but not manage roles).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2016 at 10:26 am
Where I work, we use Octopus and Chef for automated deployments. If what you need is an account that can create a database, create objects within that, and then assign users access and permissions on that, then the dbcreator server role can do this as it becomes owner of the databases it creates.
NOTE: The account does not have access to other databases it didn't create, however, it can drop other databases. I have confirmed by adding a dbcreator account on my local 2014 instance.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 9, 2016 at 10:49 am
Thom A (12/9/2016)
Robin35 (12/9/2016)
Thom A (12/9/2016)
Correct me if I'm wrong, but are you instead saying that this particular login should always be assigned specific permissions whenever you create any and all databases on an instance, and these permissions should be assigned at the point of creation?This particular login should have the ability to GRANT access to other logins or users on all the databases.
Ok, so as I said, a login with the server role Securityadmin doesn't give any rights at a database level. You'd need to give it a user on the database, and then the db_securityadmin role on that database. They then have the ability the GRANT (or REVOKE/DENY) permissions on the database (but not manage roles).
Thanks Thom. so when i grant db_securityadmin access on db level, can this login assign db_owner access for other users on a database ?
December 9, 2016 at 10:50 am
Eric M Russell (12/9/2016)
Where I work, we use Octopus and Chef for automated deployments. If what you need is an account that can create a database, create objects within that, and then assign users access and permissions on that, then the dbcreator server role can do this as it becomes owner of the databases it creates.NOTE: The account does not have access to other databases it didn't create, however, it can drop other databases. I have confirmed by adding a dbcreator account on my local 2014 instance.
we are planning to implement the Octopus deploy in our environment but it's not live yet and also not sure if it's applicable to existing database ?
December 9, 2016 at 11:30 am
Robin35 (12/9/2016)
Eric M Russell (12/9/2016)
Where I work, we use Octopus and Chef for automated deployments. If what you need is an account that can create a database, create objects within that, and then assign users access and permissions on that, then the dbcreator server role can do this as it becomes owner of the databases it creates.NOTE: The account does not have access to other databases it didn't create, however, it can drop other databases. I have confirmed by adding a dbcreator account on my local 2014 instance.
we are planning to implement the Octopus deploy in our environment but it's not live yet and also not sure if it's applicable to existing database ?
For deployment accounts, I've been granting account membership in dbcreator server role and then for any pre-existing databases for which the account may deploy to, I add account to those specific db_owner database roles. However, it depends on the nature of your deployments.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply