SQL Server Login for a Windows Group
-
Here is a scenario (simplified for the question):
1) I have three databases on a single instance of SQL Server.
2) Each of these databases have Windows Groups associated and cooresponding Logins.
For example: DB1_Admin, DB1_ReadWrite, DB1_ReadOnly
These logins have the default database set as DB1
DB2_Admin, DB2_ReadWrite, DB2_ReadOnly
These logins have the default database set as DB2
DB3_Admin, DB3_ReadWrite, DB3_ReadOnly
These logins have the default database set as DB3
3) Windows users are members of the appropriate Windows Groups which grants them the appropriate access to the databases.
For example: Bob Smith is a member of DB1_ReadWrite to which that login
for SQL Server allows him access to DB1 with R/W permissions
The extended scenario: In the above example in #3: Bob Smith is also a member of DB3_Admin and a member of DB2_ReadOnly. When Bob logs into the instance via SQL Server Management Studio, his default database appears as DB2. If DB2 was offline, he is unable to connect.
The question: How can I control which database is Bob's default when he logs into the instance via SSMS? What determines the precedence of these logins?
-
when u create the login you can specify the default database.
For the particular login you can change the default database later on also.
-
a login has only one default database, even when he has access to multiple db's thru different roles;
ig you go to SSMS>>Security>>Logins, you can find either his domain username or the group;
when you right click and go to Properties, there is a drop down list, second from the bottom, that you can use to select the default database for the user.
does that help?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you for the replies; but they are not the scenarios that I am describing. Please let me describe this a bit more:
- Bob Smith has a Windows Active Directory account of "MyNetwork\bsmith".
- Bob's Windows AD account is a member of three Windows Groups:
- DB1_Admin
- DB2_RW
- DB3_ReadOnly
- These three Windows Groups have corresponding logins in SQL Server (these are not roles).
(as described in the previous replies)
- DB1_Admin: Default Database: DB1
- DB2_RW: Default Database: DB2
- DB3_ReadOnly: Default Database DB3
- When Bob uses SSMS to work with these databases, he sees all of them and has the appropriate permissions that are associated with the said logins. The drop down list in the upper left corner of SSMS displays DB2. He is able to select one of the other databases just fine.
- Then one day, the DB2 database goes offline. Bob opens SSMS and receives a "cannot login" error despite his membership in Windows Groups that have corresponding logins for other databases on the instance.
I am seeking to understand the precedence that each login has in this scenario as well as how to gain more control in this scenario.
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply