Solved

public user - SQL Server 2008 R2

Posted on 2014-03-19
1
330 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 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

12 Experts available now in Live!

Get 1:1 Help Now