• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

permissions type in syspermissions

what would be the equivalent of type of server_permissions in syspermissions?

Thanks
0
anushahanna
Asked:
anushahanna
  • 5
  • 5
2 Solutions
 
mcv22Commented:
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
 
anushahannaAuthor Commented:
>>SQL server 2000 didn't have a concept of server level permissions

sorry.. actually I meant database_permissions.type, instead.
0
 
mcv22Commented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
anushahannaAuthor Commented:
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
 
mcv22Commented:
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
 
anushahannaAuthor Commented:
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
 
mcv22Commented:
The link might not have all permission types supported by SQL server 2005. What are the permission types that your select returns.
0
 
anushahannaAuthor Commented:
yes, handful of connects,updates and 100s of executes and selects
0
 
mcv22Commented:
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
 
anushahannaAuthor Commented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now