newbie - user permissions

I have added a new user to a database without any explicit permissions, but when I view their effective permissions inside the Microsoft SQL Server Management Studio, they have a whole host of permissions. How can this be? Is it a bug in SQL Server? Or could it be that the public role has all these permissions?

If new users are inheriting these permissions from the public role, how do I view the public role permissions?

Thanks.

To view the public role permissions use the following query

select class_desc,major_id,minor_id,permission_name,state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('public')

For more information see the documentation on the catalog view sys.database_permissions at http://msdn2.microsoft.com/en-us/library/ms188367.aspx

|||

Hi,

did you create a Windows User account or a SQL Server user account ? If you created a SQl Server user Account, then the rights probably belong to the public role. You can view the permissions by naviogating to the Database --> Security --> Database Roles --> public . If you added a Windows user this might also apply as well as the user can be in a Windows group which was added and has the appropiate permissions. All permissions are then inherited from the group the user is in. grant permissions are additive while deny′s are restrictive which means that once denied you cannot do the command this eas applied on.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

This is what I did (all using the Microsoft SQL Server Management Studio):

- Firstly I created a new login (Security -> Logins -> New Login) selecting Windows authentication. Whilst still in the New Login dialog, I selected the User Mappings page and mapped the user to the desired database (leaving membership to the public database role only).

- I then opened the properties for the database that I mapped the new login to, selected the new user and clicked 'Effective Permissions'. The list displayed many more permissions than just "connect".

I have added this user to another SQL Server on the network in exactly the same way and everything worked fine (i.e. the permissions listed were just "Connect").

Any ideas? Thanks again.

|||

As Jens mentioned, the user is probably a member of a group that is granted permissions in this database. You can look at the permissions directly granted to the user in the database by executing:

select class_desc, major_id, minor_id, permission_name, state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('your_user_name')

If this only shows connect, then the other permissions are inherited from a group. You can look more closely at the database_permissions catalog to see which group is granted the extra set of permissions.

Thanks
Laurentiu

|||

Using the query you provided I have determined that the user is only directly granted connect.

Do you know of a query I can run that will tell me the source of the other permissions the user has?

|||

You can check to see what groups/roles you have in the database by querying sys.database_principals. You can then query sys.database_permissions for each group/role to which your user belongs to, to see what permissions these are granted. Or you could work the other way around and check to see who is the grantee of the permissions that you see in Management Studio. To see what Windows groups your user belongs to, you can check the sys.login_token catalog. To see what roles he belongs to, you can query sys.database_role_members.

For example, here are some queries that you may find useful:

-- Find groups to which current user belongs to in current database
--
select lt.name from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid and dbp.type = 'G'

-- Find roles to which current user belongs to in current database
--
select rls.name from sys.database_role_members dbrm, sys.database_principals rls
where dbrm.role_principal_id = rls.principal_id and dbrm.member_principal_id = user_id()

-- Find permissions granted to current user directly or that are inherited
-- from groups or roles
--
select permission_name from sys.database_permissions
where grantee_principal_id in
(
select user_id()
union
select dbp.principal_id from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid
union
select dbrm.role_principal_id from sys.database_role_members dbrm
where dbrm.member_principal_id = user_id()
)

If you need more help, please include the output of these queries with your next reply.

Thanks
Laurentiu

|||

Hi Laurentiu:

Could you please show us on how to get the login name, the username and the role to which the login belongs (db_ddladmin, db_datareader etc) in all the databases?. This query when run from master should show the output as:

Loginname usename databasename Rolename

This should show the output listed by each db.

Also is there a way to script the roles to which the user belongs. I did not see that option in SSMS. In 2000 the option was the checkboxes script users and script database roles.

Please let us know.

Thank you

AK

|||

This query will get the login, it's user name, and database role. Run this in each database, and you have your answer. Although it's not a complete picture if you use Windows Authentication. If you have a windows group as a login, members of that group won't get mapped to a database user until needed and will not show up in this query. But they still have access.

select sl.name [loginname], dbm.name [username], db_name() [databasename], dbr.name [rolename]
from sys.server_principals sl inner join sys.database_principals dbm
on (sl.sid = dbm.sid)
inner join sys.database_role_members dbrm
on (dbm.principal_id = dbrm.member_principal_id)
inner join sys.database_principals dbr
on (dbrm.role_principal_id = dbr.principal_id)

-Jack

|||Thanks a lot Jack. I can modify it on my end according to my needs. Thanks again.