Granting CONTROL permission on a database

Question

Granting CONTROL permission on a database

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
July 16, 2018 at 10:43 pm

#73762

Comments posted to this topic are about the item Granting CONTROL permission on a database

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72626 More actions · Answer 1

Nice, easy question, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 2

In 2014, I discovered, you can execute the GRANT statement without the user existing in the database, and SQL Server will obligingly add the user. However, the user will be disabled (i.e. won't be able to connect) until you issue a GRANT CONNECT.

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Roland Alexander STL - Tuesday, July 17, 2018 5:07 AM
In 2014, I discovered, you can execute the GRANT statement without the user existing in the database, and SQL Server will obligingly add the user. However, the user will be disabled (i.e. won't be able to connect) until you issue a GRANT CONNECT.

Try testing on a new databases that has no users or groups added. I believe that is the behavior if that user is a member of a group that already exists in the database.
Otherwise you should be getting the error Cannot find the user 'SomeUserName', because it does not exist or you do not have permission.

Sue

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 4

Sue_H - Wednesday, July 18, 2018 12:05 PM
Roland Alexander STL - Tuesday, July 17, 2018 5:07 AM
In 2014, I discovered, you can execute the GRANT statement without the user existing in the database, and SQL Server will obligingly add the user. However, the user will be disabled (i.e. won't be able to connect) until you issue a GRANT CONNECT.
Try testing on a new databases that has no users or groups added. I believe that is the behavior if that user is a member of a group that already exists in the database.
Otherwise you should be getting the error Cannot find the user 'SomeUserName', because it does not exist or you do not have permission.
Sue

Actually, the user was based on a group which existed at the server level, but was not in the database, nor were any of its members, as far as I could tell. I'll dig a little deeper.

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer