Solved

permissions type in syspermissions

Posted on 2010-08-23
10
407 Views
Last Modified: 2012-05-10
what would be the equivalent of type of server_permissions in syspermissions?

Thanks
0
Comment
Question by:anushahanna
  • 5
  • 5
10 Comments
 
LVL 12

Expert Comment

by:mcv22
Comment Utility
AFAIK, SQL server 2000 didn't have a concept of server level permissions. It was introduced starting with SQL server 2005. In fact MS recommends not using the backward compatibility views like syspermissions. sys.server_permissions should suffice if you are using SQL server 2005 or higher.
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
>>SQL server 2000 didn't have a concept of server level permissions

sorry.. actually I meant database_permissions.type, instead.
0
 
LVL 12

Accepted Solution

by:
mcv22 earned 500 total points
Comment Utility
You need to join the syspermissions table with the sysobjects table on syspermissions.id = sysobjects.id and sysobjects.xtype specifies the type of object.

For more information on types of objects, refer to : http://msdn.microsoft.com/en-us/library/ms177596.aspx
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
thanks. but we are after the permission type not the object.

for example, in sql 2005, it might be like:
select count(*) from sys.database_principals pr  JOIN sys.database_permissions
ON principal_id = grantee_principal_id where pr.type= 'in'
0
 
LVL 12

Assisted Solution

by:mcv22
mcv22 earned 500 total points
Comment Utility
Couldn't find documentation on the MS website, but I hope this link is of some value:

http://www.tek-tips.com/viewthread.cfm?qid=1488839&page=10
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Author Comment

by:anushahanna
Comment Utility
Thanks. Just to test, I ran the query on a sql 2005 database, and it brought back 0 records, but

select count(*) from sys.database_principals pr  JOIN sys.database_permissions ON principal_id = grantee_principal_id  brings back a few hundred records..

how would you interpret it?
0
 
LVL 12

Expert Comment

by:mcv22
Comment Utility
The link might not have all permission types supported by SQL server 2005. What are the permission types that your select returns.
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
yes, handful of connects,updates and 100s of executes and selects
0
 
LVL 12

Expert Comment

by:mcv22
Comment Utility
Try to work something along the lines of this:

select *
from syspermissions sp
join sysobjects so
on sp.id = so.id
join sysusers su
on sp.grantee = su.uid
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
select count(*) from sys.database_principals pr  JOIN sys.database_permissions pe
ON principal_id = grantee_principal_id brings 400, your query brings around 300.

are you able to identity anything close for sys.database_permissions.type?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

15 Experts available now in Live!

Get 1:1 Help Now