March 31, 2010 at 4:50 pm
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
March 31, 2010 at 6:47 pm
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.
March 31, 2010 at 7:54 pm
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
March 31, 2010 at 10:27 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply