New Role with permissions not being carried down to new login

Question

New Role with permissions not being carried down to new login

TRACEY-320982

SSChampion

Points: 13653
More actions
March 31, 2010 at 4:50 pm

#142422

I created a new Role
CREATE ROLE app_USER AUTHORIZATION db_securityadmin;
GRANT CREATE DEFAULT TO app_USER;
GRANT CREATE PROCEDURE TO app_USER;
GRANT CREATE RULE TO app_USER;
GRANT CREATE TABLE TO app_USER;
GRANT CREATE VIEW TO app_USER;
GRANT CREATE SCHEMA TO app_USER;
When adding a new db login called testuser and selecting the role app_user in the GUI SQL Studio or running via script.
After I right click on database and click on properties, permissions find testuser , the create table, create view is not selected.
USE [master]
GO
CREATE LOGIN [testuser] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestDB]
GO
CREATE USER [testuser] FOR LOGIN [testuser]
GO
USE [TestDB]
GO
ALTER USER [testuser] WITH DEFAULT_SCHEMA=[testuser]
GO
USE [TestDB]
GO
CREATE SCHEMA [testuser] AUTHORIZATION [testuser]
GO
USE [TestDB]
GO
EXEC sp_addrolemember N'app_USER', N'testuser'
GO
Any ideas I thought the role permissions come down to the user. SQL 2005

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

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

Glenn Dorling SSCertifiable Points: 7876 More actions · Answer 1

That is the correct behaviour.

When looking at permissions for a login or role through SSMS you only see what has been explicitly assigned to it, not what implicit rights it may have inherited from group membership.

When you look at the permissions for the testuser user you won't see anything. When you look at the app_user role though you see what has been assigned to that role, which is what testuser also has, but implicitly rather than explicitly.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

Let me look at the role and see the permissions. Thanks

What system tables is this perhaps i could write a query to see the create table rights

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

This link may be of some use to you. It shows a script that will perform a complete security audit for you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events