Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

public user - SQL Server 2008 R2

Posted on 2014-03-19
1
Medium Priority
?
361 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 40

Accepted Solution

by:
lcohan earned 1500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

571 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