6

In SQL Server Management Studio, when I right click my database and click properties, a window opens showing the permissions users have on the database. I have recently added a user to active directory with the intention of granting this user permission on my database. The problem is, in active directory, I see a lot of users, but in SQL Server Management Studio, I only see very few of these users. The user I just created doesn't show up either when I hit search->browse or when I search for the exact name. What am I doing wrong? Shouldn't SQL Server be able to connect with Active Directory to get the user list? Additionally, it seems like all of the users that are showing up, are all from one group in AD...

Edit

just saying thanks all for the answers...

Summary of answers and the solution

Users need to be added to the main Security->Logins folder BEFORE they show up in a particular database's Properties->Permissions->Users listing. However, since I did not have enough permissions on SQL Server itself, when I went to the main logins folder, I could not see users (even though they were there for my colleague who had more permissions). But since I DID have enough permissions on the database, I COULD see the users there, leading to my confusion.

Improve this question

3 Answers 3

Reset to default
10

You need to add their Windows account to the SQL Server instance first. Go to the security folder for the instance, not the database. Add the user you want as a new login there. Once they've been added to the SQL Server instance you can assign them permissions on individual databases.

Improve this answer
5
  • Then why is it that in my Database's Security->Users folder there are users listed there, but those users are NOT listed in the "main" Security folder? (The main security folder being the one at the root/the same level as the databases folder)
    – Kyle
    Apr 21, 2011 at 14:45
  • @KyleM This can happen if a database is restored to a different SQL Server instance where the logins have not yet been created at the server level. Database level security from the original SQL Server instance is carried over with restored databases.
    – squillman
    Apr 21, 2011 at 14:48
  • @squillman In this case we're going from domain A (test environment) to domain B (production environment). So I believe whoever created the DB permissions (now gone from company) created them ON the production environment.. the usernames are completely different on A and B... does that change anything you said?
    – Kyle
    Apr 21, 2011 at 14:57
  • @KyleM: That's exactly what it means. The logins were created on one server in another AD domain. When the database was migrated it was migrated without the logins so the users remain in the database but don't have a corresponding login, they're orphaned. You can delete the orphaned users from the database and create new SQL logins and grant them access to the database, which will create the corresponding users in the database.
    – joeqwerty
    Apr 21, 2011 at 15:01
  • @joe the database wasn't migrated, the scripts were re-run on domain B...
    – Kyle
    Apr 21, 2011 at 15:08
5

Are you referring to database users or SQL logins? A database user is based on a SQL login, which needs to be created first. When adding a SQL login you have the choice of using Windows authentication or SQL authentication. If you choose Windows authentication you can browse for a local user or group account or a domain user or group account. Once you've created the login you can then grant that login access to a database which will create the user in the database.

Users and logins don't just show up, they have to be created.

Improve this answer
6
  • See my comment to Dynamo. There are users listed under "My Database->right click->click Properties->View the permissions tab". Those users are not listed under the SQL Server's main "Security->Users" folder, nor do I see them listed elsewhere. So where can I find them then?
    – Kyle
    Apr 21, 2011 at 14:50
  • I'm just trying to give a user permissions to read from and write to my SQL Database. I don't care how that is accomplished, although I do think I'll need to use Windows Authentication, since the user comes from Active Directory. I hope this doesn't seem like I'm being rude, because I am trying, I'm just confused as to how these users got there in the Database's Security->Users folder and how I can add more users. Since you guys are telling me to "Add a SQL login" first, I'm trying to figure out why, then, can't I find "SQL Logins" for the other users in my DB's Security->Users folder..
    – Kyle
    Apr 21, 2011 at 14:52
  • It sounds like the users you're seeing in the database are orphaned, which can happen in a number of different ways, such as deleting a SQL login without dropping the user from the database. In order to add a new user to the databases you need to create a SQL login first. You can create either a Windows login or a SQL login. You're not restricted to creating only Windows logins just because the server is an AD member.
    – joeqwerty
    Apr 21, 2011 at 14:59
  • It turns out I didn't have enough permission to see the users in the main Security folder, but they were in fact there, as my colleague saw when she logged in. Thanks. I'd upvote you for your help, but I cannot due to my reputation.
    – Kyle
    Apr 21, 2011 at 15:40
  • Glad to help...
    – joeqwerty
    Apr 21, 2011 at 15:53
3

Basically what you should be doing is add the ADuser as a SQL Server login(so that he will be authenticated to the server) and create a user in the database who is associated with the login and give this user a permission to do stuff(read, write, update etc) on your database(authorization). You can acieve this using somethingin in line of this:

USE [master]
GO
CREATE LOGIN [Domain\User] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [YourTargetDatabase]
GO
CREATE USER [Domain\User] FOR LOGIN [Domain\User]
GO
USE [YourTargetDatabase]
GO
EXEC sp_addrolemember N'db_datareader', N'Domain\User'
GO
USE [YourTargetDatabase]
GO
EXEC sp_addrolemember N'db_datawriter', N'Domain\User'
GO

Sometimes you can see users which are not associated with any logins due to the fact that the database which is taken in another server might be restored here or the login is deleted with out removing the associated users.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .