Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

server role and permission Expand / Collapse
Author
Message
Posted Wednesday, January 22, 2014 1:25 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 789, Visits: 694
sqlfriends (1/21/2014)
By reading from the definition from microsoft site below, it look like we grant view definition on server level, also add the user to the database without granting any permissions is equivalent as granting the user the view definition to the securables.


No. Try this (replace ssgrep with any database you might have):

USE master
go
CREATE LOGIN bludder WITH PASSWORD = 'offentligt'
GRANT VIEW ANY DEFINITION TO bludder
go
EXECUTE AS LOGIN = 'bludder'
go
SELECT * FROM ssgrep.sys.tables
go
REVERT
go
DROP LOGIN bludder

This produces the error message

Server: Msg 916, Level 14, State 1, Line 1
The server principal "bludder" is not able to access the database "ssgrep" under the current security context.


The example also shows how you easily can test a certain permission scenario easily.

To create a user without creating a schema, use CREATE USER.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1533490
Posted Wednesday, January 22, 2014 9:45 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 1,755, Visits: 3,165
Thanks, tried the way you test, it looks like if the user is added to the database without any other permissions, and it has server role view server definition, it will work.

I will also use create user to replace sp_adduser in the last statment.


Thanks much
Post #1533734
Posted Wednesday, January 22, 2014 10:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 1,755, Visits: 3,165
I rewrite the script as this:

USE master
GRANT VIEW SERVER STATE TO [mydomain\sys$swind]
GRANT VIEW ANY DEFINITION TO [mydomain\sys$swind]
GRANT EXECUTE ON xp_readerrorlog TO [mydomain\sys$swind]
EXECUTE sp_MSforeachdb 'USE [?]; CREATE USER [mydomain\sys$swind] FOR LOGIN [mydomain\sys$swind]'
USE msdb
EXEC sp_addrolemember N'db_datareader', [mydomain\sys$swind]

It works great.

And by replacing sp_adduser with create user, there is no schema created for the user for all the databases except one - master, why in master database the user still own a schema that is the same name as the login name?


Thanks

Post #1533758
Posted Wednesday, January 22, 2014 2:25 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 789, Visits: 694
sqlfriends (1/22/2014)And by replacing sp_adduser with create user, there is no schema created for the user for all the databases except one - master, why in master database the user still own a schema that is the same name as the login name?


I was not able to repeat this. Maybe this is something the application does already at installation?


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1533837
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse