July 17, 2012 at 3:27 am
I need to find out accress and permission of Two users Mark and Mike on a database.
Both users should just have READ ACCESS on teh database
Mark
1. In the server security node -> Logins i dont see this user present. There is only 1 login present called SQLALLUSERS but I am not able see if Mark is mapped to this login
2. Under database Security Node I cannot see this User again
Mike
1. In the server security node -> Logins i dont see this user present. As I said there is only 1 login present called SQLALLUSERS but I am not able see if Mike is mapped to this login
2. Under database Security Node I DO see Mike. On checking properties i can see he is assigned to db_datareader role
I need to give both of them Read Access. I need to know what to do for each
Also Would like to know why I am not ale to see Users mapped to login. I though i should be able to see Mark and Mike somewhere assigned to SQLALLUSERS. Let me know if any other info is required
July 17, 2012 at 8:11 am
I think my question was too dumb or just did not make sense because i dint get a single reply. Please guy help me out. Any hint could be helpful
July 17, 2012 at 8:21 am
you will need to create logins and map them to your current users or create new users.
http://msdn.microsoft.com/en-us/library/aa337552
The reason you have users in a database and no associated logins could be that the database was restored from a different server or the logins and user were created on current server and then the login was dropped but not the user.
July 17, 2012 at 8:46 am
thanks a ton for replying.
In given stuation where there is no login do you think Mike will be able to have READ Access to databse. I have asked him and he has. This means that orphanned users also work with out any issues. It that right??
Also I need to able able to add Mark to db_datareader role so he could also have READ ACCESS. When I do so it says I do not have access. Do i need to be in role db_owner to be able to add users to a role
July 17, 2012 at 8:57 am
If there is not a login Mike should not be able to connect to the server. In SQL server 2012 there is a new feature called contained databases which allow for users without logins to access a database.
run this in the database in question. It will telll you whether the user Mike is mapped to a login on the server.
select l.name as loginname, u.name as username
from sys.server_principals l
inner join sys.database_principals u
on l.sid = u.sid
where u.name = 'Mike'
for adding permissions you would need either db_owner or db_securityadmin (http://msdn.microsoft.com/en-us/library/ms189612%28SQL.105%29.aspx)
July 17, 2012 at 9:05 am
This login "SQLALLUSERS"... Is it a group (icon shows 2 people) or a single user (icon simply shows 1 person)? Is it a domain user/group (domainname\SQLALLUSERS)? What server level permissions does this login have?
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply