Solved

public user - SQL Server 2008 R2

Posted on 2014-03-19
1
332 Views
Last Modified: 2014-03-25
Good afternoon,

I ran the query listed below to retrieve the permissions that have been granted explicitly to users:

select usr.name as UserName,
case when perm.state <> 'W' then perm.state_desc else 'GRANT' end as PerType,
schema_name(perm.major_id) as SchemaName,
perm.permission_name as PermissionName,
Schema_name(obj.schema_id) as ObjectSchemaName,
obj.name as ObjectName,
user_name(obj.principal_id) as ObjectOwner,
perm.class_desc as ClassDescription,
obj.type_desc as ObjectType,
case when cl.column_id is null then '--' else cl.name end as ColName,
case when perm.state = 'W' then 'X' else '--' end as 'IsGrantOption'
from sys.database_permissions as perm
left outer join sys.all_objects as obj
on perm.major_id = obj.[object_id]
inner join sys.database_principals as usr
on perm.grantee_principal_id = usr.principal_id
left join sys.columns as cl
on cl.column_id = perm.minor_id and cl.[object_id] = perm.major_id
order by UserName asc
go

The query works fine but I do have a question:

Is this query enough to determine if other explicit permissions --besides those granted by default-- have been granted to the public database user? I have a left join with the columns and the objects so I am aware that I'll get more permissions than those that are particular to the database that I am evaluating.

In addition, since I am somewhat new to SQL Server in general, I was wondering what the risks are, if any, with having the public user with permissions to views, tables, stored procedures from the "sys" object schema name?

Thanks!
0
Comment
Question by:VicBel
1 Comment
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 39940369
you can simply run

exec sp_helprotect;

against your SQL database in order to get accurate security information.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now