lists through sp_helpsrvrolemember return unexpected results

When running the stored procedure sp_helpsrvrolemember to return a list of
users with 'sysadmin' permissions, my result set was inconsistent. Users tha
t
had the permissions removed weeks ago still showed up, however, when running
the query hours later, the results set changed and did not reflect the old
permissions.
Is this a known bug, a cache issue that needs restarts?
(This is a SQL 2000 sp4 instance)
DougCan you recreate that with a test database? I'm not able to do that here on
my system.
"Doug Olson" wrote:

> When running the stored procedure sp_helpsrvrolemember to return a list of
> users with 'sysadmin' permissions, my result set was inconsistent. Users t
hat
> had the permissions removed weeks ago still showed up, however, when runni
ng
> the query hours later, the results set changed and did not reflect the old
> permissions.
> Is this a known bug, a cache issue that needs restarts?
> (This is a SQL 2000 sp4 instance)
> Doug|||I haven't been able to exactly re-create it, however, I have received some
communication/feedback that this is related to a ghosting issue/problem.
Doug
"Buck Woody - Microsoft SQL Server Team" wrote:
[vbcol=seagreen]
> Can you recreate that with a test database? I'm not able to do that here o
n
> my system.
> "Doug Olson" wrote:
>|||Good stuff - so do you have this solved?
"Doug Olson" wrote:
[vbcol=seagreen]
> I haven't been able to exactly re-create it, however, I have received some
> communication/feedback that this is related to a ghosting issue/problem.
> Doug
> "Buck Woody - Microsoft SQL Server Team" wrote:
>

Listing permissions on Stored Procedures via query.

How do I list the permissions on a stored procedure with a query?
I'm trying to remove Public from some xp_* procedures but I'd like to see
what Public is assigned to first.
Thanks much.Look up the system procedure sp_helprotect in SQL Server Books Online.
Anith|||"Horst" <Horst@.discussions.microsoft.com> wrote in message
news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> How do I list the permissions on a stored procedure with a query?
> I'm trying to remove Public from some xp_* procedures but I'd like to see
> what Public is assigned to first.
>
> Thanks much.
You could reverse engineer the sp_helprotect sproc, but this should work
for you as well.
--
USE master
GO
CREATE TABLE #Foo (
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType varchar(50),
Action varchar(50),
[Column] sysname NULL
)
INSERT #Foo
EXEC sp_helprotect
SELECT *
FROM #Foo
WHERE OBJECT LIKE 'xp%'
AND Grantee = 'public'
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the help.
"Rick Sawtell" wrote:

> "Horst" <Horst@.discussions.microsoft.com> wrote in message
> news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> You could reverse engineer the sp_helprotect sproc, but this should work
> for you as well.
> --
> USE master
> GO
>
> CREATE TABLE #Foo (
> Owner sysname,
> Object sysname,
> Grantee sysname,
> Grantor sysname,
> ProtectType varchar(50),
> Action varchar(50),
> [Column] sysname NULL
> )
> INSERT #Foo
> EXEC sp_helprotect
> SELECT *
> FROM #Foo
> WHERE OBJECT LIKE 'xp%'
> AND Grantee = 'public'
> DROP TABLE #Foo
> --
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Thanks for the help.
"Anith Sen" wrote:

> Look up the system procedure sp_helprotect in SQL Server Books Online.
> --
> Anith
>
>

List Windows Userids Object Permission

I am looking for a script that will list windows userids and sql userids
with database object permissions.
Please help me with this task.
Thanks,Hi
Look at the sysusers, syspermissions and sysprotects tables
John
"Joe K." wrote:

> I am looking for a script that will list windows userids and sql userids
> with database object permissions.
> Please help me with this task.
> Thanks,

List User's Permissions down to table.column action

You might try this SP. This SP generates the necessary T-SQL commands to ad
d
a new user based on an existing user. You can look at the generated T_SQL t
o
review the permissions. I beleave it does column level permission. If
nothing else you can use this code to build some thing that works for you.
I wrote an article about this code here that you can read if you have a
subscription to SQL Server Magazine:
http://www.sqlmag.com/Article/Artic...rver_41181.html
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure
dbo. USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN Script Date: 10/21/2002
6:56:14 AM ******/
CREATE PROCEDURE USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN
@.OLDLOGIN VARCHAR(200), -- EXISTING LOGIN NAME
@.NEWLOGIN VARCHAR(200), -- NEW LOGIN NAME
@.NEWUSER CHAR(128) , -- NEW USER NAME FOR EACH DATABASE
@.PASSWORD VARCHAR(200) ='' -- PASSWORD FOR SQL SERVER AUTHENTICATED USERS
AS
--
-- WRITTEN BY: GREG LARSEN FOR WASHINTON STATE DEPARTMENT OF HEALTH
-- DATE: DECEMBER 13, 2001
--
-- DESCRIPTION: THIS STORED PROCEDURE GENERATES COMMANDS
-- THAT WILL ADD A NEW USERS TO THE SERVER WITH THE SAME
-- RIGHTS AS AN EXISTING USER.
--
-- UPDATE HISTORY:
-- DATE: OCTOBER 21, 2002
-- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING.
--
-- BEGIN CALLOUT A
-- Declare variables and create temporary table to hold commands
----
--
DECLARE @.INDX INT
SET NOCOUNT ON
DECLARE @.TEXT CHAR(100)
DECLARE @.CNT INT
DECLARE @.CMD NVARCHAR(200)
DECLARE @.DB NVARCHAR(128)
DECLARE @.OLDUSER VARCHAR(100)
--
-- CREATE TABLE TO HOLD GENERATED COMMANDS
--
CREATE TABLE #TMP_LOGIN_RIGHTS (
RIGHTS_TEXT CHAR(2000))
-- END CALLOUT A
-- BEGIN CALLOUT B
-- ADD USER TO SERVER
----
-
-- TEST TO SEE IF #OLDOGIN EXISTS ON SERVER
--
SELECT @.CNT=COUNT(*) FROM [MASTER].[DBO].[SYSLOGINS] WHERE LOGIN
NAME =
@.OLDLOGIN
IF @.CNT = 0
BEGIN
RAISERROR ('@.OLDLOGIN IS NOT A VALID USER OF SQL SERVER',16,1)
RETURN
END
--
-- DETERMINE IF @.NEWLOGIN IS ALREADY DEFINED TO SERVER
--
SELECT @.CNT=COUNT(*) FROM [MASTER].[DBO].[SYSLOGINS] WHERE LOGIN
NAME =
@.NEWLOGIN
--
-- IF @.NEWLOGIN EXIST ABORT
--
IF @.CNT > 0
BEGIN
RAISERROR('@.NEWLOGIN ALREADY EXISTS ON SQL SERVER', 16,1)
RETURN
END
--
-- IF @.NEWLOGIN CONTAINS '' THEN NT LOGIN
--
SELECT @.INDX=CHARINDEX('',@.NEWLOGIN)
IF @.INDX > 0
--
-- GENERATE COMMANDS TO ADD NT USER
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
[MASTER].[DBO].[SP_GRANTLOGIN] [' + @.NEWLOGIN + ']'
+ CHAR(13)+
'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] [' + @.NEWLOGIN + '],&
#91;' +
RTRIM(DBNAME) + ']' AS RIGHTS_TEXT
FROM [MASTER].[DBO].[SYSLOGINS]
WHERE LOGINNAME = @.OLDLOGIN
ELSE
BEGIN
IF @.PASSWORD = ''
BEGIN
RAISERROR('@.PASSWORD MUST BE SPECIFIED FOR SQL SERVER AUTHENTICATION',
16,1)
RETURN
END -- @.PASSWORD = ''
--
-- GENERATE COMMANDS TO ADD SQL SERVER AUTHENTICATION USER
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
[MASTER].[DBO].[SP_ADDLOGIN] [' + @.NEWLOGIN +
'],[' + @.PASSWORD + ']' + CHAR(13)+
'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] [' + @.NEWLOGIN + '],
[' +
RTRIM(DBNAME) + ']' AS RIGHTS_TEXT
FROM [MASTER].[DBO].[SYSLOGINS]
WHERE LOGINNAME = @.OLDLOGIN
END
-- END CALLOUT B
-- BEGIN CALLOUT C
-- ADD USER TO DATABASES
----
--
SET NOCOUNT ON
SET @.CMD= '[MASTER].[DBO].[SP_HELPUSER]'
--
-- GET THE NAME OF ALL DATABASES
--
DECLARE ALLDATABASES CURSOR FOR
SELECT NAME FROM [MASTER].[DBO].[SYSDATABASES]
OPEN ALLDATABASES
FETCH NEXT FROM ALLDATABASES INTO @.DB
--
-- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE
--
CREATE TABLE #TMPUSERS (
USERNAME VARCHAR(100),
GROUPNAME VARCHAR(100),
LOGINNAME VARCHAR(100),
DEFDBNAME VARCHAR(100),
USERID SMALLINT,
SUSERID SMALLINT
)
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--
-- COMMAND TO RETURN ALL USERS IN DATABASE
--
SET @.CMD = '[' + @.DB + ']' + '.[DBO].[SP_HELPUSER]'
--
-- GET ALL USERS IN DATABASE INTO TEMPORARY TABLE
--
INSERT INTO #TMPUSERS EXEC (@.CMD)
--
-- DETERMINE WHETHER OLD USER IS IN DATABASE
--
SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE LOGINNAME = @.OLDLOGIN
--
-- IF OLD USER IS IN DATABASE THEN ADD NEW USER TO DATABASE
--
IF @.CNT > 0
BEGIN
--
-- DETERMINE IF @.NEWUSER ALREADY EXIST IN DATABASE
--
SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE USERNAME = @.NEWUSER
--
-- IF USER EXIST ABORT
--
IF @.CNT > 0
BEGIN
--
-- CLOSE AND DEALLOCATE CURSOR OF DATABASES SO NEXT TIME AROUND NO
ERROR OCCURS
--
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
--
-- SET TEXT OF ERROR MESSAGE
--
SET @.TEXT = '@.NEWUSER ALREADY EXIST IN DATABASE ' + @.DB
-- RAISE ERROR AND RETURN
RAISERROR(@.TEXT,16,1)
RETURN
END
--
-- GENERATE COMMAND TO ADD @.NEWLOGIN TO CURRENT DATABASE
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE [' + @.DB +
'].[DBO].[SP_GRANTDBACCESS] [' +
@.NEWLOGIN +
'],[' + RTRIM(@.NEWUSER) + ']' AS RIGHTS_TEXT
FROM (
SELECT DISTINCT USERNAME, LOGINNAME
FROM #TMPUSERS
WHERE LOGINNAME = @.OLDLOGIN )A
END
--
-- TRUNCATE TABLE FOR NEXT DATABASE
--
TRUNCATE TABLE #TMPUSERS
-- GET NEXT DATABASE
--
FETCH NEXT FROM ALLDATABASES INTO @.DB
END -- WHILE (@.@.FETCH_STATUS = 0)
--
-- CLOSE CURSOR OF DATABASES
--
CLOSE ALLDATABASES
-- END CALLOUT C
-- BEGIN CALLOUT D
-- GRANT USER TO ROLES WITHIN DATABASES
----
--
OPEN ALLDATABASES
FETCH NEXT FROM ALLDATABASES INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--
-- SET COMMAND TO FIND USER PERMISSIONS HAS IN CURRENT DATABASE
--
SET @.CMD = '[' + @.DB + '].[DBO].[SP_HELPUSER]'
--
-- EMPTY TEMPORARY TABLE #TMPUSERS
--
TRUNCATE TABLE #TMPUSERS
--
-- GET USER PERMISSIONS FOR ALL USERS IN CURRENT DATABASE
--
INSERT INTO #TMPUSERS EXEC (@.CMD)
--
-- DETERMINE WHETHER THE OLD USER IS IN A ROLE
--
SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE LOGINNAME = @.OLDLOGIN AND
GROUPNAME <> 'PUBLIC'
--
-- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT ROLE
--
IF @.CNT > 0
--
-- GENERATE COMMANDS TO ADD @.NEWUSER TO APPROPRIATE ROLES IN CURRENT DATABAS
E
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT DISTINCT 'EXECUTE [' + @.DB +
'].[DBO].[SP_ADDROLEMEMBER] [' + RTRIM(A.GROUPNAME) +
'],[' + RTRIM(@.NEWUSER) + ']' AS RIGHTS_TEXT
FROM #TMPUSERS A
WHERE A.LOGINNAME = @.OLDLOGIN AND A.GROUPNAME <>
'PUBLIC'
--
-- GET NEXT DATABASE
--
FETCH NEXT FROM ALLDATABASES INTO @.DB
END -- WHILE (@.@.FETCH_STATUS = 0)
CLOSE ALLDATABASES
DROP TABLE #TMPUSERS
-- END CALLOUT D
-- BEGIN CALLOUT E
-- GRANT USER ACCESS TO SERVER ROLES
----
-
-- CREATE TABLE TO HOLD SERVER ROLES
--
CREATE TABLE #TMPSRVROLES (
SERVERROLE VARCHAR(100),
MEMBERNAME VARCHAR(100),
MEMBERSID VARBINARY (85))
--
-- COMMAND TO GET SERVER ROLES
--
SET @.CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'
--
-- GET SERVER ROLES INTO TEMPORARY TABLE
--
INSERT INTO #TMPSRVROLES EXEC (@.CMD)
--
-- DETERMINE WHETHER THE OLD USER IS IN A SERVER ROLE
--
SELECT @.CNT = COUNT(*) FROM #TMPSRVROLES WHERE MEMBERNAME = @.OLDLOGIN
--
-- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT SERVER ROLE
--
IF @.CNT > 0
--
-- GENERATE COMMANDS TO ADD @.NEWLOGIN INTO THE APPROPRIATE SERVER ROLES
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
[MASTER].[DBO].[SP_ADDSRVROLEMEMBER] ' + '[' +
RTRIM(@.NEWLOGIN) + ']' +
',[' + RTRIM(A.SERVERROLE) + ']' AS RIGHTS_TEXT
FROM #TMPSRVROLES A
WHERE A.MEMBERNAME = @.OLDLOGIN
--
-- DROP SERVER ROLE TABLE
--
DROP TABLE #TMPSRVROLES
-- END CALLOUT E
-- BEGIN CALLOUT F
-- GRANT USER PERMISSIONS TO OBJECTS AND STATEMENTS
----
--
-- CREATE TEMPORARY TABLE TO HOLD INFORMATION ABOUT OBJECTS PERMISSIONS
--
CREATE TABLE #TMPPROTECT (
OWNER VARCHAR(100),
OBJECT VARCHAR(100),
GRANTEE VARCHAR(100),
GRANTOR VARCHAR(100),
PROTECTTYPE CHAR(10),
ACTION VARCHAR(20),
COLUMNX VARCHAR(100))
OPEN ALLDATABASES
SET @.CMD = 'SELECT @.OLDUSER=NAME FROM [' + @.DB +
'].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM
[MASTER].[DBO].[SYSLOGINS] WHERE LOGINNAME = ' +
CHAR(39) + @.OLDLOGIN + CHAR(39) + ')'
FETCH NEXT FROM ALLDATABASES INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--
-- INITIALIZE @.OLDUSER VARIABLE
--
SET @.OLDUSER = ''
--
--GENERATE COMMAND TO SET @.OLDUSER TO THE USER NAME IN DATABASE
-- IF @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
--
SET @.CMD = 'SELECT @.OLDUSER=NAME FROM [' + @.DB +
'].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM
[MASTER].[DBO].[SYSLOGINS] WHERE LOGINNAME = ' +
CHAR(39) + @.OLDLOGIN + CHAR(39) + ')'
--
-- EXECUTE COMMAND TO SET @.OLDUSER TO THE USER NAME IN DATABASE
-- IF @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
--
EXEC [MASTER].[DBO].[SP_EXECUTESQL] @.CMD,N'@.OLDUSER CHAR(200)
OUTPUT',@.OLDUSER OUT
--
-- IF @.OLDUSER IS NOT BLANK THEN @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
--
IF @.OLDUSER <> ''
BEGIN
--
-- GENERATE COMMAND TO GET OBJECT PERMISSIONS FOR CURRENT DATABASE
--
SET @.CMD = '[' + @.DB + '].[DBO].[SP_HELPROTECT]'
--
-- GET OBJECT PERMISSIONS INTO TEMPORARY TABLE
--
INSERT INTO #TMPPROTECT EXEC (@.CMD)
--
-- DETERMINE IF THERE ARE ANY OBJECT PERMISSIONS FOR @.OLDUSER
--
SELECT @.CNT = COUNT(*) FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER
IF @.CNT > 0
--
-- SWITCH TO THE APPROPRIATE DATABASE
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'USE [' + @.DB + ']'
--
-- GENERATE COMMANDS TO GRANT OBJECTS PERMISSIONS FOR REFERENCES, SELECT,
UPDATE TO @.NEWUSER
--
INSERT INTO #TMP_LOGIN_RIGHTS
SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
'] TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
OPTION'
ELSE
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
'] TO [' + RTRIM(@.NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT <> '.'
AND COLUMNX = '(ALL+NEW)'
--
-- GRANT COLUMN PERMISSION ON OBJECTS
--
INSERT INTO #TMP_LOGIN_RIGHTS
SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
']([' + COLUMNX + '])' +
' TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
OPTION'
ELSE
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
']([' + COLUMNX + '])' +
' TO [' + RTRIM(@.NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER
AND OBJECT <> '.' AND COLUMNX <> '(ALL+NEW)'
AND COLUMNX <> '.'
--
-- GRANT INSERT, DELETE, AND EXECUTE PERMISSION ON OBJECTS
--
INSERT INTO #TMP_LOGIN_RIGHTS
SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
'] TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
OPTION'
ELSE
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
'] TO [' + RTRIM(@.NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT <> '.' AND
ACTION IN ('INSERT', 'DELETE', 'EXECUTE') AND COLUMNX = '.'
--
-- GRANT STATEMENT PERMISSIONS
--
--
INSERT INTO #TMP_LOGIN_RIGHTS
SELECT 'GRANT ' +
ACTION +
' TO [' + RTRIM(@.NEWUSER) + ']'
AS RIGHTS_TEXT
FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT = '.'
-- REMOVE RECORDS FOR TEMPORARY TABLE IN PREPARATION FOR THE NEXT DATABASE
TO BE PROCESSES
--
TRUNCATE TABLE #TMPPROTECT
END
--
-- GET NEXT DATABASE TO PROCESS
--
FETCH NEXT FROM ALLDATABASES INTO @.DB
END -- WHILE (@.@.FETCH_STATUS = 0)
--
-- CLOSE AND DEALLOCATE DATABASE LIST CURSOR
--
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
--
-- DROP TEMPORARY TABLE THAT HELD OBJECT PERMISSIONS
--
DROP TABLE #TMPPROTECT
-- END CALLOUT F
-- BEGIN CALLOUT G
-- PROCESS ALL GENERATED COMMANDS ONE AT A TIME
----
--
--
-- GET ALL THE GENERATED COMMANDS
--
DECLARE COMMANDS CURSOR FOR
SELECT * FROM #TMP_LOGIN_RIGHTS
OPEN COMMANDS
FETCH NEXT FROM COMMANDS INTO @.CMD
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--
-- PRINT COMMAND TO BE PROCESSED
--
PRINT @.CMD
--
-- UNCOMMENT IF YOU WANT THE STORED PROCEDURE TO EXECUTE THE COMMANDS TO ADD
THE PERMISSIONS
--
--EXEC (@.CMD)
--
-- GET NEXT COMMAND
--
FETCH NEXT FROM COMMANDS INTO @.CMD
END -- WHILE (@.@.FETCH_STATUS = 0)
--
-- CLOSE AND DEALLOCATE COMMAND CURSOR
--
CLOSE COMMANDS
DEALLOCATE COMMANDS
--
-- DROP TABLE THAT HELD THE GENERATED RIGHTS THAT WHERE GRANTED TO @.NEWLOGIN
--
DROP TABLE #TMP_LOGIN_RIGHTS
-- END CALLOUT G
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Neil Meyer" wrote:

> Anybody have a script to list a user's effective permissions down to the
> table.column [action] level?
> With records produced by this script, I would be able to say for example:
> userID has select and update permissions on dbo.cdata.cfid through public
> role
> I saw a similar, recent post on a similar topic on this newsgroup, but the
> suggestions did not work for me.
> Thanks anybody.
>
>Anybody have a script to list a user's effective permissions down to the
table.column [action] level?
With records produced by this script, I would be able to say for example:
userID has select and update permissions on dbo.cdata.cfid through public
role
I saw a similar, recent post on a similar topic on this newsgroup, but the
suggestions did not work for me.
Thanks anybody.|||You might try this SP. This SP generates the necessary T-SQL commands to ad
d
a new user based on an existing user. You can look at the generated T_SQL t
o
review the permissions. I beleave it does column level permission. If
nothing else you can use this code to build some thing that works for you.
I wrote an article about this code here that you can read if you have a
subscription to SQL Server Magazine:
http://www.sqlmag.com/Article/Artic...rver_41181.html
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure
dbo. USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN Script Date: 10/21/2002
6:56:14 AM ******/
CREATE PROCEDURE USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN
@.OLDLOGIN VARCHAR(200), -- EXISTING LOGIN NAME
@.NEWLOGIN VARCHAR(200), -- NEW LOGIN NAME
@.NEWUSER CHAR(128) , -- NEW USER NAME FOR EACH DATABASE
@.PASSWORD VARCHAR(200) ='' -- PASSWORD FOR SQL SERVER AUTHENTICATED USERS
AS
--
-- WRITTEN BY: GREG LARSEN FOR WASHINTON STATE DEPARTMENT OF HEALTH
-- DATE: DECEMBER 13, 2001
--
-- DESCRIPTION: THIS STORED PROCEDURE GENERATES COMMANDS
-- THAT WILL ADD A NEW USERS TO THE SERVER WITH THE SAME
-- RIGHTS AS AN EXISTING USER.
--
-- UPDATE HISTORY:
-- DATE: OCTOBER 21, 2002
-- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING.
--
-- BEGIN CALLOUT A
-- Declare variables and create temporary table to hold commands
----
--
DECLARE @.INDX INT
SET NOCOUNT ON
DECLARE @.TEXT CHAR(100)
DECLARE @.CNT INT
DECLARE @.CMD NVARCHAR(200)
DECLARE @.DB NVARCHAR(128)
DECLARE @.OLDUSER VARCHAR(100)
--
-- CREATE TABLE TO HOLD GENERATED COMMANDS
--
CREATE TABLE #TMP_LOGIN_RIGHTS (
RIGHTS_TEXT CHAR(2000))
-- END CALLOUT A
-- BEGIN CALLOUT B
-- ADD USER TO SERVER
----
-
-- TEST TO SEE IF #OLDOGIN EXISTS ON SERVER
--
SELECT @.CNT=COUNT(*) FROM [MASTER].[DBO].[SYSLOGINS] WHERE LOGIN
NAME =
@.OLDLOGIN
IF @.CNT = 0
BEGIN
RAISERROR ('@.OLDLOGIN IS NOT A VALID USER OF SQL SERVER',16,1)
RETURN
END
--
-- DETERMINE IF @.NEWLOGIN IS ALREADY DEFINED TO SERVER
--
SELECT @.CNT=COUNT(*) FROM [MASTER].[DBO].[SYSLOGINS] WHERE LOGIN
NAME =
@.NEWLOGIN
--
-- IF @.NEWLOGIN EXIST ABORT
--
IF @.CNT > 0
BEGIN
RAISERROR('@.NEWLOGIN ALREADY EXISTS ON SQL SERVER', 16,1)
RETURN
END
--
-- IF @.NEWLOGIN CONTAINS '' THEN NT LOGIN
--
SELECT @.INDX=CHARINDEX('',@.NEWLOGIN)
IF @.INDX > 0
--
-- GENERATE COMMANDS TO ADD NT USER
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
[MASTER].[DBO].[SP_GRANTLOGIN] [' + @.NEWLOGIN + ']'
+ CHAR(13)+
'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] [' + @.NEWLOGIN + '],&
#91;' +
RTRIM(DBNAME) + ']' AS RIGHTS_TEXT
FROM [MASTER].[DBO].[SYSLOGINS]
WHERE LOGINNAME = @.OLDLOGIN
ELSE
BEGIN
IF @.PASSWORD = ''
BEGIN
RAISERROR('@.PASSWORD MUST BE SPECIFIED FOR SQL SERVER AUTHENTICATION',
16,1)
RETURN
END -- @.PASSWORD = ''
--
-- GENERATE COMMANDS TO ADD SQL SERVER AUTHENTICATION USER
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
[MASTER].[DBO].[SP_ADDLOGIN] [' + @.NEWLOGIN +
'],[' + @.PASSWORD + ']' + CHAR(13)+
'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] [' + @.NEWLOGIN + '],
[' +
RTRIM(DBNAME) + ']' AS RIGHTS_TEXT
FROM [MASTER].[DBO].[SYSLOGINS]
WHERE LOGINNAME = @.OLDLOGIN
END
-- END CALLOUT B
-- BEGIN CALLOUT C
-- ADD USER TO DATABASES
----
--
SET NOCOUNT ON
SET @.CMD= '[MASTER].[DBO].[SP_HELPUSER]'
--
-- GET THE NAME OF ALL DATABASES
--
DECLARE ALLDATABASES CURSOR FOR
SELECT NAME FROM [MASTER].[DBO].[SYSDATABASES]
OPEN ALLDATABASES
FETCH NEXT FROM ALLDATABASES INTO @.DB
--
-- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE
--
CREATE TABLE #TMPUSERS (
USERNAME VARCHAR(100),
GROUPNAME VARCHAR(100),
LOGINNAME VARCHAR(100),
DEFDBNAME VARCHAR(100),
USERID SMALLINT,
SUSERID SMALLINT
)
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--
-- COMMAND TO RETURN ALL USERS IN DATABASE
--
SET @.CMD = '[' + @.DB + ']' + '.[DBO].[SP_HELPUSER]'
--
-- GET ALL USERS IN DATABASE INTO TEMPORARY TABLE
--
INSERT INTO #TMPUSERS EXEC (@.CMD)
--
-- DETERMINE WHETHER OLD USER IS IN DATABASE
--
SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE LOGINNAME = @.OLDLOGIN
--
-- IF OLD USER IS IN DATABASE THEN ADD NEW USER TO DATABASE
--
IF @.CNT > 0
BEGIN
--
-- DETERMINE IF @.NEWUSER ALREADY EXIST IN DATABASE
--
SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE USERNAME = @.NEWUSER
--
-- IF USER EXIST ABORT
--
IF @.CNT > 0
BEGIN
--
-- CLOSE AND DEALLOCATE CURSOR OF DATABASES SO NEXT TIME AROUND NO
ERROR OCCURS
--
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
--
-- SET TEXT OF ERROR MESSAGE
--
SET @.TEXT = '@.NEWUSER ALREADY EXIST IN DATABASE ' + @.DB
-- RAISE ERROR AND RETURN
RAISERROR(@.TEXT,16,1)
RETURN
END
--
-- GENERATE COMMAND TO ADD @.NEWLOGIN TO CURRENT DATABASE
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE [' + @.DB +
'].[DBO].[SP_GRANTDBACCESS] [' +
@.NEWLOGIN +
'],[' + RTRIM(@.NEWUSER) + ']' AS RIGHTS_TEXT
FROM (
SELECT DISTINCT USERNAME, LOGINNAME
FROM #TMPUSERS
WHERE LOGINNAME = @.OLDLOGIN )A
END
--
-- TRUNCATE TABLE FOR NEXT DATABASE
--
TRUNCATE TABLE #TMPUSERS
-- GET NEXT DATABASE
--
FETCH NEXT FROM ALLDATABASES INTO @.DB
END -- WHILE (@.@.FETCH_STATUS = 0)
--
-- CLOSE CURSOR OF DATABASES
--
CLOSE ALLDATABASES
-- END CALLOUT C
-- BEGIN CALLOUT D
-- GRANT USER TO ROLES WITHIN DATABASES
----
--
OPEN ALLDATABASES
FETCH NEXT FROM ALLDATABASES INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--
-- SET COMMAND TO FIND USER PERMISSIONS HAS IN CURRENT DATABASE
--
SET @.CMD = '[' + @.DB + '].[DBO].[SP_HELPUSER]'
--
-- EMPTY TEMPORARY TABLE #TMPUSERS
--
TRUNCATE TABLE #TMPUSERS
--
-- GET USER PERMISSIONS FOR ALL USERS IN CURRENT DATABASE
--
INSERT INTO #TMPUSERS EXEC (@.CMD)
--
-- DETERMINE WHETHER THE OLD USER IS IN A ROLE
--
SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE LOGINNAME = @.OLDLOGIN AND
GROUPNAME <> 'PUBLIC'
--
-- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT ROLE
--
IF @.CNT > 0
--
-- GENERATE COMMANDS TO ADD @.NEWUSER TO APPROPRIATE ROLES IN CURRENT DATABAS
E
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT DISTINCT 'EXECUTE [' + @.DB +
'].[DBO].[SP_ADDROLEMEMBER] [' + RTRIM(A.GROUPNAME) +
'],[' + RTRIM(@.NEWUSER) + ']' AS RIGHTS_TEXT
FROM #TMPUSERS A
WHERE A.LOGINNAME = @.OLDLOGIN AND A.GROUPNAME <>
'PUBLIC'
--
-- GET NEXT DATABASE
--
FETCH NEXT FROM ALLDATABASES INTO @.DB
END -- WHILE (@.@.FETCH_STATUS = 0)
CLOSE ALLDATABASES
DROP TABLE #TMPUSERS
-- END CALLOUT D
-- BEGIN CALLOUT E
-- GRANT USER ACCESS TO SERVER ROLES
----
-
-- CREATE TABLE TO HOLD SERVER ROLES
--
CREATE TABLE #TMPSRVROLES (
SERVERROLE VARCHAR(100),
MEMBERNAME VARCHAR(100),
MEMBERSID VARBINARY (85))
--
-- COMMAND TO GET SERVER ROLES
--
SET @.CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'
--
-- GET SERVER ROLES INTO TEMPORARY TABLE
--
INSERT INTO #TMPSRVROLES EXEC (@.CMD)
--
-- DETERMINE WHETHER THE OLD USER IS IN A SERVER ROLE
--
SELECT @.CNT = COUNT(*) FROM #TMPSRVROLES WHERE MEMBERNAME = @.OLDLOGIN
--
-- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT SERVER ROLE
--
IF @.CNT > 0
--
-- GENERATE COMMANDS TO ADD @.NEWLOGIN INTO THE APPROPRIATE SERVER ROLES
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
[MASTER].[DBO].[SP_ADDSRVROLEMEMBER] ' + '[' +
RTRIM(@.NEWLOGIN) + ']' +
',[' + RTRIM(A.SERVERROLE) + ']' AS RIGHTS_TEXT
FROM #TMPSRVROLES A
WHERE A.MEMBERNAME = @.OLDLOGIN
--
-- DROP SERVER ROLE TABLE
--
DROP TABLE #TMPSRVROLES
-- END CALLOUT E
-- BEGIN CALLOUT F
-- GRANT USER PERMISSIONS TO OBJECTS AND STATEMENTS
----
--
-- CREATE TEMPORARY TABLE TO HOLD INFORMATION ABOUT OBJECTS PERMISSIONS
--
CREATE TABLE #TMPPROTECT (
OWNER VARCHAR(100),
OBJECT VARCHAR(100),
GRANTEE VARCHAR(100),
GRANTOR VARCHAR(100),
PROTECTTYPE CHAR(10),
ACTION VARCHAR(20),
COLUMNX VARCHAR(100))
OPEN ALLDATABASES
SET @.CMD = 'SELECT @.OLDUSER=NAME FROM [' + @.DB +
'].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM
[MASTER].[DBO].[SYSLOGINS] WHERE LOGINNAME = ' +
CHAR(39) + @.OLDLOGIN + CHAR(39) + ')'
FETCH NEXT FROM ALLDATABASES INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--
-- INITIALIZE @.OLDUSER VARIABLE
--
SET @.OLDUSER = ''
--
--GENERATE COMMAND TO SET @.OLDUSER TO THE USER NAME IN DATABASE
-- IF @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
--
SET @.CMD = 'SELECT @.OLDUSER=NAME FROM [' + @.DB +
'].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM
[MASTER].[DBO].[SYSLOGINS] WHERE LOGINNAME = ' +
CHAR(39) + @.OLDLOGIN + CHAR(39) + ')'
--
-- EXECUTE COMMAND TO SET @.OLDUSER TO THE USER NAME IN DATABASE
-- IF @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
--
EXEC [MASTER].[DBO].[SP_EXECUTESQL] @.CMD,N'@.OLDUSER CHAR(200)
OUTPUT',@.OLDUSER OUT
--
-- IF @.OLDUSER IS NOT BLANK THEN @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
--
IF @.OLDUSER <> ''
BEGIN
--
-- GENERATE COMMAND TO GET OBJECT PERMISSIONS FOR CURRENT DATABASE
--
SET @.CMD = '[' + @.DB + '].[DBO].[SP_HELPROTECT]'
--
-- GET OBJECT PERMISSIONS INTO TEMPORARY TABLE
--
INSERT INTO #TMPPROTECT EXEC (@.CMD)
--
-- DETERMINE IF THERE ARE ANY OBJECT PERMISSIONS FOR @.OLDUSER
--
SELECT @.CNT = COUNT(*) FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER
IF @.CNT > 0
--
-- SWITCH TO THE APPROPRIATE DATABASE
--
INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'USE [' + @.DB + ']'
--
-- GENERATE COMMANDS TO GRANT OBJECTS PERMISSIONS FOR REFERENCES, SELECT,
UPDATE TO @.NEWUSER
--
INSERT INTO #TMP_LOGIN_RIGHTS
SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
'] TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
OPTION'
ELSE
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
'] TO [' + RTRIM(@.NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT <> '.'
AND COLUMNX = '(ALL+NEW)'
--
-- GRANT COLUMN PERMISSION ON OBJECTS
--
INSERT INTO #TMP_LOGIN_RIGHTS
SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
']([' + COLUMNX + '])' +
' TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
OPTION'
ELSE
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
']([' + COLUMNX + '])' +
' TO [' + RTRIM(@.NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER
AND OBJECT <> '.' AND COLUMNX <> '(ALL+NEW)'
AND COLUMNX <> '.'
--
-- GRANT INSERT, DELETE, AND EXECUTE PERMISSION ON OBJECTS
--
INSERT INTO #TMP_LOGIN_RIGHTS
SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
'] TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
OPTION'
ELSE
'GRANT ' +
ACTION +' ON [' +
@.DB + '].[' + OWNER + '].[' + OBJECT +
'] TO [' + RTRIM(@.NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT <> '.' AND
ACTION IN ('INSERT', 'DELETE', 'EXECUTE') AND COLUMNX = '.'
--
-- GRANT STATEMENT PERMISSIONS
--
--
INSERT INTO #TMP_LOGIN_RIGHTS
SELECT 'GRANT ' +
ACTION +
' TO [' + RTRIM(@.NEWUSER) + ']'
AS RIGHTS_TEXT
FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT = '.'
-- REMOVE RECORDS FOR TEMPORARY TABLE IN PREPARATION FOR THE NEXT DATABASE
TO BE PROCESSES
--
TRUNCATE TABLE #TMPPROTECT
END
--
-- GET NEXT DATABASE TO PROCESS
--
FETCH NEXT FROM ALLDATABASES INTO @.DB
END -- WHILE (@.@.FETCH_STATUS = 0)
--
-- CLOSE AND DEALLOCATE DATABASE LIST CURSOR
--
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
--
-- DROP TEMPORARY TABLE THAT HELD OBJECT PERMISSIONS
--
DROP TABLE #TMPPROTECT
-- END CALLOUT F
-- BEGIN CALLOUT G
-- PROCESS ALL GENERATED COMMANDS ONE AT A TIME
----
--
--
-- GET ALL THE GENERATED COMMANDS
--
DECLARE COMMANDS CURSOR FOR
SELECT * FROM #TMP_LOGIN_RIGHTS
OPEN COMMANDS
FETCH NEXT FROM COMMANDS INTO @.CMD
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--
-- PRINT COMMAND TO BE PROCESSED
--
PRINT @.CMD
--
-- UNCOMMENT IF YOU WANT THE STORED PROCEDURE TO EXECUTE THE COMMANDS TO ADD
THE PERMISSIONS
--
--EXEC (@.CMD)
--
-- GET NEXT COMMAND
--
FETCH NEXT FROM COMMANDS INTO @.CMD
END -- WHILE (@.@.FETCH_STATUS = 0)
--
-- CLOSE AND DEALLOCATE COMMAND CURSOR
--
CLOSE COMMANDS
DEALLOCATE COMMANDS
--
-- DROP TABLE THAT HELD THE GENERATED RIGHTS THAT WHERE GRANTED TO @.NEWLOGIN
--
DROP TABLE #TMP_LOGIN_RIGHTS
-- END CALLOUT G
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Neil Meyer" wrote:

> Anybody have a script to list a user's effective permissions down to the
> table.column [action] level?
> With records produced by this script, I would be able to say for example:
> userID has select and update permissions on dbo.cdata.cfid through public
> role
> I saw a similar, recent post on a similar topic on this newsgroup, but the
> suggestions did not work for me.
> Thanks anybody.
>
>|||Works great! Thanks Greg. Well done.
Neil
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:9F537B10-3A04-4C01-9006-4086C1F2F9FF@.microsoft.com...[vbcol=seagreen]
> You might try this SP. This SP generates the necessary T-SQL commands to
> add
> a new user based on an existing user. You can look at the generated T_SQL
> to
> review the permissions. I beleave it does column level permission. If
> nothing else you can use this code to build some thing that works for you.
> I wrote an article about this code here that you can read if you have a
> subscription to SQL Server Magazine:
> http://www.sqlmag.com/Article/Artic...rver_41181.html
>
>
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
> /****** Object: Stored Procedure
> dbo. USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN Script Date: 10/21/2002
> 6:56:14 AM ******/
>
>
> CREATE PROCEDURE USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN
> @.OLDLOGIN VARCHAR(200), -- EXISTING LOGIN NAME
> @.NEWLOGIN VARCHAR(200), -- NEW LOGIN NAME
> @.NEWUSER CHAR(128) , -- NEW USER NAME FOR EACH DATABASE
> @.PASSWORD VARCHAR(200) ='' -- PASSWORD FOR SQL SERVER AUTHENTICATED USERS
> AS
> --
> -- WRITTEN BY: GREG LARSEN FOR WASHINTON STATE DEPARTMENT OF HEALTH
> -- DATE: DECEMBER 13, 2001
> --
> -- DESCRIPTION: THIS STORED PROCEDURE GENERATES COMMANDS
> -- THAT WILL ADD A NEW USERS TO THE SERVER WITH THE SAME
> -- RIGHTS AS AN EXISTING USER.
> --
> -- UPDATE HISTORY:
> -- DATE: OCTOBER 21, 2002
> -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING.
> --
> -- BEGIN CALLOUT A
> -- Declare variables and create temporary table to hold commands
> ----
--
> DECLARE @.INDX INT
> SET NOCOUNT ON
> DECLARE @.TEXT CHAR(100)
> DECLARE @.CNT INT
> DECLARE @.CMD NVARCHAR(200)
> DECLARE @.DB NVARCHAR(128)
> DECLARE @.OLDUSER VARCHAR(100)
> --
> -- CREATE TABLE TO HOLD GENERATED COMMANDS
> --
> CREATE TABLE #TMP_LOGIN_RIGHTS (
> RIGHTS_TEXT CHAR(2000))
> -- END CALLOUT A
> -- BEGIN CALLOUT B
> -- ADD USER TO SERVER
> ----
--
> -- TEST TO SEE IF #OLDOGIN EXISTS ON SERVER
> --
> SELECT @.CNT=COUNT(*) FROM [MASTER].[DBO].[SYSLOGINS] WHERE LOG
INNAME =
> @.OLDLOGIN
> IF @.CNT = 0
> BEGIN
> RAISERROR ('@.OLDLOGIN IS NOT A VALID USER OF SQL SERVER',16,1)
> RETURN
> END
> --
> -- DETERMINE IF @.NEWLOGIN IS ALREADY DEFINED TO SERVER
> --
> SELECT @.CNT=COUNT(*) FROM [MASTER].[DBO].[SYSLOGINS] WHERE LOG
INNAME =
> @.NEWLOGIN
> --
> -- IF @.NEWLOGIN EXIST ABORT
> --
> IF @.CNT > 0
> BEGIN
> RAISERROR('@.NEWLOGIN ALREADY EXISTS ON SQL SERVER', 16,1)
> RETURN
> END
> --
> -- IF @.NEWLOGIN CONTAINS '' THEN NT LOGIN
> --
> SELECT @.INDX=CHARINDEX('',@.NEWLOGIN)
> IF @.INDX > 0
> --
> -- GENERATE COMMANDS TO ADD NT USER
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
> [MASTER].[DBO].[SP_GRANTLOGIN] [' + @.NEWLOGIN + ']'
> + CHAR(13)+
> 'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] [' + @.NEWLOGIN +
'],[' +
> RTRIM(DBNAME) + ']' AS RIGHTS_TEXT
> FROM [MASTER].[DBO].[SYSLOGINS]
> WHERE LOGINNAME = @.OLDLOGIN
> ELSE
> BEGIN
> IF @.PASSWORD = ''
> BEGIN
> RAISERROR('@.PASSWORD MUST BE SPECIFIED FOR SQL SERVER AUTHENTICATION',
> 16,1)
> RETURN
> END -- @.PASSWORD = ''
> --
> -- GENERATE COMMANDS TO ADD SQL SERVER AUTHENTICATION USER
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
> [MASTER].[DBO].[SP_ADDLOGIN] [' + @.NEWLOGIN +
> '],[' + @.PASSWORD + ']' + CHAR(13)+
> 'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] [' + @.NEWLOGIN +
'],[' +
> RTRIM(DBNAME) + ']' AS RIGHTS_TEXT
> FROM [MASTER].[DBO].[SYSLOGINS]
> WHERE LOGINNAME = @.OLDLOGIN
> END
> -- END CALLOUT B
> -- BEGIN CALLOUT C
> -- ADD USER TO DATABASES
> ----
--
> SET NOCOUNT ON
> SET @.CMD= '[MASTER].[DBO].[SP_HELPUSER]'
> --
> -- GET THE NAME OF ALL DATABASES
> --
> DECLARE ALLDATABASES CURSOR FOR
> SELECT NAME FROM [MASTER].[DBO].[SYSDATABASES]
> OPEN ALLDATABASES
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> --
> -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE
> --
> CREATE TABLE #TMPUSERS (
> USERNAME VARCHAR(100),
> GROUPNAME VARCHAR(100),
> LOGINNAME VARCHAR(100),
> DEFDBNAME VARCHAR(100),
> USERID SMALLINT,
> SUSERID SMALLINT
> )
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --
> -- COMMAND TO RETURN ALL USERS IN DATABASE
> --
> SET @.CMD = '[' + @.DB + ']' + '.[DBO].[SP_HELPUSER]'
> --
> -- GET ALL USERS IN DATABASE INTO TEMPORARY TABLE
> --
> INSERT INTO #TMPUSERS EXEC (@.CMD)
> --
> -- DETERMINE WHETHER OLD USER IS IN DATABASE
> --
> SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE LOGINNAME = @.OLDLOGIN
> --
> -- IF OLD USER IS IN DATABASE THEN ADD NEW USER TO DATABASE
> --
> IF @.CNT > 0
> BEGIN
> --
> -- DETERMINE IF @.NEWUSER ALREADY EXIST IN DATABASE
> --
> SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE USERNAME = @.NEWUSER
> --
> -- IF USER EXIST ABORT
> --
> IF @.CNT > 0
> BEGIN
> --
> -- CLOSE AND DEALLOCATE CURSOR OF DATABASES SO NEXT TIME AROUND NO
> ERROR OCCURS
> --
> CLOSE ALLDATABASES
> DEALLOCATE ALLDATABASES
> --
> -- SET TEXT OF ERROR MESSAGE
> --
> SET @.TEXT = '@.NEWUSER ALREADY EXIST IN DATABASE ' + @.DB
> -- RAISE ERROR AND RETURN
> RAISERROR(@.TEXT,16,1)
> RETURN
> END
> --
> -- GENERATE COMMAND TO ADD @.NEWLOGIN TO CURRENT DATABASE
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE [' + @.DB +
> '].[DBO].[SP_GRANTDBACCESS] [' +
> @.NEWLOGIN +
> '],[' + RTRIM(@.NEWUSER) + ']' AS RIGHTS_TEXT
> FROM (
> SELECT DISTINCT USERNAME, LOGINNAME
> FROM #TMPUSERS
> WHERE LOGINNAME = @.OLDLOGIN )A
> END
> --
> -- TRUNCATE TABLE FOR NEXT DATABASE
> --
> TRUNCATE TABLE #TMPUSERS
> --
> -- GET NEXT DATABASE
> --
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> END -- WHILE (@.@.FETCH_STATUS = 0)
> --
> -- CLOSE CURSOR OF DATABASES
> --
> CLOSE ALLDATABASES
> -- END CALLOUT C
> -- BEGIN CALLOUT D
> -- GRANT USER TO ROLES WITHIN DATABASES
> ----
--
> OPEN ALLDATABASES
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --
> -- SET COMMAND TO FIND USER PERMISSIONS HAS IN CURRENT DATABASE
> --
> SET @.CMD = '[' + @.DB + '].[DBO].[SP_HELPUSER]'
> --
> -- EMPTY TEMPORARY TABLE #TMPUSERS
> --
> TRUNCATE TABLE #TMPUSERS
> --
> -- GET USER PERMISSIONS FOR ALL USERS IN CURRENT DATABASE
> --
> INSERT INTO #TMPUSERS EXEC (@.CMD)
> --
> -- DETERMINE WHETHER THE OLD USER IS IN A ROLE
> --
> SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE LOGINNAME = @.OLDLOGIN AND
> GROUPNAME <> 'PUBLIC'
> --
> -- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT ROLE
> --
> IF @.CNT > 0
> --
> -- GENERATE COMMANDS TO ADD @.NEWUSER TO APPROPRIATE ROLES IN CURRENT
> DATABASE
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT DISTINCT 'EXECUTE [' + @.DB
+
> '].[DBO].[SP_ADDROLEMEMBER] [' + RTRIM(A.GROUPNAME)
+
> '],[' + RTRIM(@.NEWUSER) + ']' AS RIGHTS_TEXT
> FROM #TMPUSERS A
> WHERE A.LOGINNAME = @.OLDLOGIN AND A.GROUPNAME <>
> 'PUBLIC'
> --
> -- GET NEXT DATABASE
> --
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> END -- WHILE (@.@.FETCH_STATUS = 0)
> CLOSE ALLDATABASES
> DROP TABLE #TMPUSERS
> -- END CALLOUT D
> -- BEGIN CALLOUT E
> -- GRANT USER ACCESS TO SERVER ROLES
> ----
--
> -- CREATE TABLE TO HOLD SERVER ROLES
> --
> CREATE TABLE #TMPSRVROLES (
> SERVERROLE VARCHAR(100),
> MEMBERNAME VARCHAR(100),
> MEMBERSID VARBINARY (85))
> --
> -- COMMAND TO GET SERVER ROLES
> --
> SET @.CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'
> --
> -- GET SERVER ROLES INTO TEMPORARY TABLE
> --
> INSERT INTO #TMPSRVROLES EXEC (@.CMD)
> --
> -- DETERMINE WHETHER THE OLD USER IS IN A SERVER ROLE
> --
> SELECT @.CNT = COUNT(*) FROM #TMPSRVROLES WHERE MEMBERNAME = @.OLDLOGIN
> --
> -- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT SERVER ROLE
> --
> IF @.CNT > 0
> --
> -- GENERATE COMMANDS TO ADD @.NEWLOGIN INTO THE APPROPRIATE SERVER ROLES
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
> [MASTER].[DBO].[SP_ADDSRVROLEMEMBER] ' + '[' +
> RTRIM(@.NEWLOGIN) + ']' +
> ',[' + RTRIM(A.SERVERROLE) + ']' AS RIGHTS_TEXT
> FROM #TMPSRVROLES A
> WHERE A.MEMBERNAME = @.OLDLOGIN
> --
> -- DROP SERVER ROLE TABLE
> --
> DROP TABLE #TMPSRVROLES
> -- END CALLOUT E
> -- BEGIN CALLOUT F
> -- GRANT USER PERMISSIONS TO OBJECTS AND STATEMENTS
> ----
--
> -- CREATE TEMPORARY TABLE TO HOLD INFORMATION ABOUT OBJECTS PERMISSIONS
> --
> CREATE TABLE #TMPPROTECT (
> OWNER VARCHAR(100),
> OBJECT VARCHAR(100),
> GRANTEE VARCHAR(100),
> GRANTOR VARCHAR(100),
> PROTECTTYPE CHAR(10),
> ACTION VARCHAR(20),
> COLUMNX VARCHAR(100))
> OPEN ALLDATABASES
> SET @.CMD = 'SELECT @.OLDUSER=NAME FROM [' + @.DB +
> '].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM
> [MASTER].[DBO].[SYSLOGINS] WHERE LOGINNAME = ' +
> CHAR(39) + @.OLDLOGIN + CHAR(39) + ')'
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --
> -- INITIALIZE @.OLDUSER VARIABLE
> --
> SET @.OLDUSER = ''
> --
> --GENERATE COMMAND TO SET @.OLDUSER TO THE USER NAME IN DATABASE
> -- IF @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
> --
> SET @.CMD = 'SELECT @.OLDUSER=NAME FROM [' + @.DB +
> '].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM
> [MASTER].[DBO].[SYSLOGINS] WHERE LOGINNAME = ' +
> CHAR(39) + @.OLDLOGIN + CHAR(39) + ')'
> --
> -- EXECUTE COMMAND TO SET @.OLDUSER TO THE USER NAME IN DATABASE
> -- IF @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
> --
> EXEC [MASTER].[DBO].[SP_EXECUTESQL] @.CMD,N'@.OLDUSER CHAR(200)
> OUTPUT',@.OLDUSER OUT
> --
> -- IF @.OLDUSER IS NOT BLANK THEN @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
> --
> IF @.OLDUSER <> ''
> BEGIN
> --
> -- GENERATE COMMAND TO GET OBJECT PERMISSIONS FOR CURRENT DATABASE
> --
> SET @.CMD = '[' + @.DB + '].[DBO].[SP_HELPROTECT]'
> --
> -- GET OBJECT PERMISSIONS INTO TEMPORARY TABLE
> --
> INSERT INTO #TMPPROTECT EXEC (@.CMD)
> --
> -- DETERMINE IF THERE ARE ANY OBJECT PERMISSIONS FOR @.OLDUSER
> --
> SELECT @.CNT = COUNT(*) FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER
> IF @.CNT > 0
> --
> -- SWITCH TO THE APPROPRIATE DATABASE
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'USE [' + @.DB + ']'
> --
> -- GENERATE COMMANDS TO GRANT OBJECTS PERMISSIONS FOR REFERENCES, SELECT,
> UPDATE TO @.NEWUSER
> --
> INSERT INTO #TMP_LOGIN_RIGHTS
> SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
> THEN
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> '] TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
> OPTION'
> ELSE
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> '] TO [' + RTRIM(@.NEWUSER) + ']'
> END AS RIGHTS_TEXT
> FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT <> '.'
> AND COLUMNX = '(ALL+NEW)'
> --
> -- GRANT COLUMN PERMISSION ON OBJECTS
> --
> INSERT INTO #TMP_LOGIN_RIGHTS
> SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
> THEN
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> ']([' + COLUMNX + '])' +
> ' TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
> OPTION'
> ELSE
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> ']([' + COLUMNX + '])' +
> ' TO [' + RTRIM(@.NEWUSER) + ']'
> END AS RIGHTS_TEXT
> FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER
> AND OBJECT <> '.' AND COLUMNX <> '(ALL+NEW)'
> AND COLUMNX <> '.'
> --
> -- GRANT INSERT, DELETE, AND EXECUTE PERMISSION ON OBJECTS
> --
> INSERT INTO #TMP_LOGIN_RIGHTS
> SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
> THEN
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> '] TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
> OPTION'
> ELSE
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> '] TO [' + RTRIM(@.NEWUSER) + ']'
> END AS RIGHTS_TEXT
> FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT <> '.' AND
> ACTION IN ('INSERT', 'DELETE', 'EXECUTE') AND COLUMNX = '.'
> --
> -- GRANT STATEMENT PERMISSIONS
> --
> --
> INSERT INTO #TMP_LOGIN_RIGHTS
> SELECT 'GRANT ' +
> ACTION +
> ' TO [' + RTRIM(@.NEWUSER) + ']'
> AS RIGHTS_TEXT
> FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT = '.'
>
> --
> -- REMOVE RECORDS FOR TEMPORARY TABLE IN PREPARATION FOR THE NEXT DATABASE
> TO BE PROCESSES
> --
> TRUNCATE TABLE #TMPPROTECT
> END
> --
> -- GET NEXT DATABASE TO PROCESS
> --
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> END -- WHILE (@.@.FETCH_STATUS = 0)
> --
> -- CLOSE AND DEALLOCATE DATABASE LIST CURSOR
> --
> CLOSE ALLDATABASES
> DEALLOCATE ALLDATABASES
> --
> -- DROP TEMPORARY TABLE THAT HELD OBJECT PERMISSIONS
> --
> DROP TABLE #TMPPROTECT
> -- END CALLOUT F
> -- BEGIN CALLOUT G
> -- PROCESS ALL GENERATED COMMANDS ONE AT A TIME
> ----
--
> --
> -- GET ALL THE GENERATED COMMANDS
> --
> DECLARE COMMANDS CURSOR FOR
> SELECT * FROM #TMP_LOGIN_RIGHTS
> OPEN COMMANDS
> FETCH NEXT FROM COMMANDS INTO @.CMD
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --
> -- PRINT COMMAND TO BE PROCESSED
> --
> PRINT @.CMD
> --
> -- UNCOMMENT IF YOU WANT THE STORED PROCEDURE TO EXECUTE THE COMMANDS TO
> ADD
> THE PERMISSIONS
> --
> --EXEC (@.CMD)
> --
> -- GET NEXT COMMAND
> --
> FETCH NEXT FROM COMMANDS INTO @.CMD
> END -- WHILE (@.@.FETCH_STATUS = 0)
> --
> -- CLOSE AND DEALLOCATE COMMAND CURSOR
> --
> CLOSE COMMANDS
> DEALLOCATE COMMANDS
> --
> -- DROP TABLE THAT HELD THE GENERATED RIGHTS THAT WHERE GRANTED TO
> @.NEWLOGIN
> --
> DROP TABLE #TMP_LOGIN_RIGHTS
> -- END CALLOUT G
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "Neil Meyer" wrote:
>|||Works great! Thanks Greg. Well done.
Neil
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:9F537B10-3A04-4C01-9006-4086C1F2F9FF@.microsoft.com...[vbcol=seagreen]
> You might try this SP. This SP generates the necessary T-SQL commands to
> add
> a new user based on an existing user. You can look at the generated T_SQL
> to
> review the permissions. I beleave it does column level permission. If
> nothing else you can use this code to build some thing that works for you.
> I wrote an article about this code here that you can read if you have a
> subscription to SQL Server Magazine:
> http://www.sqlmag.com/Article/Artic...rver_41181.html
>
>
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
> /****** Object: Stored Procedure
> dbo. USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN Script Date: 10/21/2002
> 6:56:14 AM ******/
>
>
> CREATE PROCEDURE USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN
> @.OLDLOGIN VARCHAR(200), -- EXISTING LOGIN NAME
> @.NEWLOGIN VARCHAR(200), -- NEW LOGIN NAME
> @.NEWUSER CHAR(128) , -- NEW USER NAME FOR EACH DATABASE
> @.PASSWORD VARCHAR(200) ='' -- PASSWORD FOR SQL SERVER AUTHENTICATED USERS
> AS
> --
> -- WRITTEN BY: GREG LARSEN FOR WASHINTON STATE DEPARTMENT OF HEALTH
> -- DATE: DECEMBER 13, 2001
> --
> -- DESCRIPTION: THIS STORED PROCEDURE GENERATES COMMANDS
> -- THAT WILL ADD A NEW USERS TO THE SERVER WITH THE SAME
> -- RIGHTS AS AN EXISTING USER.
> --
> -- UPDATE HISTORY:
> -- DATE: OCTOBER 21, 2002
> -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING.
> --
> -- BEGIN CALLOUT A
> -- Declare variables and create temporary table to hold commands
> ----
--
> DECLARE @.INDX INT
> SET NOCOUNT ON
> DECLARE @.TEXT CHAR(100)
> DECLARE @.CNT INT
> DECLARE @.CMD NVARCHAR(200)
> DECLARE @.DB NVARCHAR(128)
> DECLARE @.OLDUSER VARCHAR(100)
> --
> -- CREATE TABLE TO HOLD GENERATED COMMANDS
> --
> CREATE TABLE #TMP_LOGIN_RIGHTS (
> RIGHTS_TEXT CHAR(2000))
> -- END CALLOUT A
> -- BEGIN CALLOUT B
> -- ADD USER TO SERVER
> ----
--
> -- TEST TO SEE IF #OLDOGIN EXISTS ON SERVER
> --
> SELECT @.CNT=COUNT(*) FROM [MASTER].[DBO].[SYSLOGINS] WHERE LOG
INNAME =
> @.OLDLOGIN
> IF @.CNT = 0
> BEGIN
> RAISERROR ('@.OLDLOGIN IS NOT A VALID USER OF SQL SERVER',16,1)
> RETURN
> END
> --
> -- DETERMINE IF @.NEWLOGIN IS ALREADY DEFINED TO SERVER
> --
> SELECT @.CNT=COUNT(*) FROM [MASTER].[DBO].[SYSLOGINS] WHERE LOG
INNAME =
> @.NEWLOGIN
> --
> -- IF @.NEWLOGIN EXIST ABORT
> --
> IF @.CNT > 0
> BEGIN
> RAISERROR('@.NEWLOGIN ALREADY EXISTS ON SQL SERVER', 16,1)
> RETURN
> END
> --
> -- IF @.NEWLOGIN CONTAINS '' THEN NT LOGIN
> --
> SELECT @.INDX=CHARINDEX('',@.NEWLOGIN)
> IF @.INDX > 0
> --
> -- GENERATE COMMANDS TO ADD NT USER
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
> [MASTER].[DBO].[SP_GRANTLOGIN] [' + @.NEWLOGIN + ']'
> + CHAR(13)+
> 'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] [' + @.NEWLOGIN +
'],[' +
> RTRIM(DBNAME) + ']' AS RIGHTS_TEXT
> FROM [MASTER].[DBO].[SYSLOGINS]
> WHERE LOGINNAME = @.OLDLOGIN
> ELSE
> BEGIN
> IF @.PASSWORD = ''
> BEGIN
> RAISERROR('@.PASSWORD MUST BE SPECIFIED FOR SQL SERVER AUTHENTICATION',
> 16,1)
> RETURN
> END -- @.PASSWORD = ''
> --
> -- GENERATE COMMANDS TO ADD SQL SERVER AUTHENTICATION USER
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
> [MASTER].[DBO].[SP_ADDLOGIN] [' + @.NEWLOGIN +
> '],[' + @.PASSWORD + ']' + CHAR(13)+
> 'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] [' + @.NEWLOGIN +
'],[' +
> RTRIM(DBNAME) + ']' AS RIGHTS_TEXT
> FROM [MASTER].[DBO].[SYSLOGINS]
> WHERE LOGINNAME = @.OLDLOGIN
> END
> -- END CALLOUT B
> -- BEGIN CALLOUT C
> -- ADD USER TO DATABASES
> ----
--
> SET NOCOUNT ON
> SET @.CMD= '[MASTER].[DBO].[SP_HELPUSER]'
> --
> -- GET THE NAME OF ALL DATABASES
> --
> DECLARE ALLDATABASES CURSOR FOR
> SELECT NAME FROM [MASTER].[DBO].[SYSDATABASES]
> OPEN ALLDATABASES
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> --
> -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE
> --
> CREATE TABLE #TMPUSERS (
> USERNAME VARCHAR(100),
> GROUPNAME VARCHAR(100),
> LOGINNAME VARCHAR(100),
> DEFDBNAME VARCHAR(100),
> USERID SMALLINT,
> SUSERID SMALLINT
> )
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --
> -- COMMAND TO RETURN ALL USERS IN DATABASE
> --
> SET @.CMD = '[' + @.DB + ']' + '.[DBO].[SP_HELPUSER]'
> --
> -- GET ALL USERS IN DATABASE INTO TEMPORARY TABLE
> --
> INSERT INTO #TMPUSERS EXEC (@.CMD)
> --
> -- DETERMINE WHETHER OLD USER IS IN DATABASE
> --
> SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE LOGINNAME = @.OLDLOGIN
> --
> -- IF OLD USER IS IN DATABASE THEN ADD NEW USER TO DATABASE
> --
> IF @.CNT > 0
> BEGIN
> --
> -- DETERMINE IF @.NEWUSER ALREADY EXIST IN DATABASE
> --
> SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE USERNAME = @.NEWUSER
> --
> -- IF USER EXIST ABORT
> --
> IF @.CNT > 0
> BEGIN
> --
> -- CLOSE AND DEALLOCATE CURSOR OF DATABASES SO NEXT TIME AROUND NO
> ERROR OCCURS
> --
> CLOSE ALLDATABASES
> DEALLOCATE ALLDATABASES
> --
> -- SET TEXT OF ERROR MESSAGE
> --
> SET @.TEXT = '@.NEWUSER ALREADY EXIST IN DATABASE ' + @.DB
> -- RAISE ERROR AND RETURN
> RAISERROR(@.TEXT,16,1)
> RETURN
> END
> --
> -- GENERATE COMMAND TO ADD @.NEWLOGIN TO CURRENT DATABASE
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE [' + @.DB +
> '].[DBO].[SP_GRANTDBACCESS] [' +
> @.NEWLOGIN +
> '],[' + RTRIM(@.NEWUSER) + ']' AS RIGHTS_TEXT
> FROM (
> SELECT DISTINCT USERNAME, LOGINNAME
> FROM #TMPUSERS
> WHERE LOGINNAME = @.OLDLOGIN )A
> END
> --
> -- TRUNCATE TABLE FOR NEXT DATABASE
> --
> TRUNCATE TABLE #TMPUSERS
> --
> -- GET NEXT DATABASE
> --
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> END -- WHILE (@.@.FETCH_STATUS = 0)
> --
> -- CLOSE CURSOR OF DATABASES
> --
> CLOSE ALLDATABASES
> -- END CALLOUT C
> -- BEGIN CALLOUT D
> -- GRANT USER TO ROLES WITHIN DATABASES
> ----
--
> OPEN ALLDATABASES
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --
> -- SET COMMAND TO FIND USER PERMISSIONS HAS IN CURRENT DATABASE
> --
> SET @.CMD = '[' + @.DB + '].[DBO].[SP_HELPUSER]'
> --
> -- EMPTY TEMPORARY TABLE #TMPUSERS
> --
> TRUNCATE TABLE #TMPUSERS
> --
> -- GET USER PERMISSIONS FOR ALL USERS IN CURRENT DATABASE
> --
> INSERT INTO #TMPUSERS EXEC (@.CMD)
> --
> -- DETERMINE WHETHER THE OLD USER IS IN A ROLE
> --
> SELECT @.CNT = COUNT(*) FROM #TMPUSERS WHERE LOGINNAME = @.OLDLOGIN AND
> GROUPNAME <> 'PUBLIC'
> --
> -- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT ROLE
> --
> IF @.CNT > 0
> --
> -- GENERATE COMMANDS TO ADD @.NEWUSER TO APPROPRIATE ROLES IN CURRENT
> DATABASE
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT DISTINCT 'EXECUTE [' + @.DB
+
> '].[DBO].[SP_ADDROLEMEMBER] [' + RTRIM(A.GROUPNAME)
+
> '],[' + RTRIM(@.NEWUSER) + ']' AS RIGHTS_TEXT
> FROM #TMPUSERS A
> WHERE A.LOGINNAME = @.OLDLOGIN AND A.GROUPNAME <>
> 'PUBLIC'
> --
> -- GET NEXT DATABASE
> --
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> END -- WHILE (@.@.FETCH_STATUS = 0)
> CLOSE ALLDATABASES
> DROP TABLE #TMPUSERS
> -- END CALLOUT D
> -- BEGIN CALLOUT E
> -- GRANT USER ACCESS TO SERVER ROLES
> ----
--
> -- CREATE TABLE TO HOLD SERVER ROLES
> --
> CREATE TABLE #TMPSRVROLES (
> SERVERROLE VARCHAR(100),
> MEMBERNAME VARCHAR(100),
> MEMBERSID VARBINARY (85))
> --
> -- COMMAND TO GET SERVER ROLES
> --
> SET @.CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'
> --
> -- GET SERVER ROLES INTO TEMPORARY TABLE
> --
> INSERT INTO #TMPSRVROLES EXEC (@.CMD)
> --
> -- DETERMINE WHETHER THE OLD USER IS IN A SERVER ROLE
> --
> SELECT @.CNT = COUNT(*) FROM #TMPSRVROLES WHERE MEMBERNAME = @.OLDLOGIN
> --
> -- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT SERVER ROLE
> --
> IF @.CNT > 0
> --
> -- GENERATE COMMANDS TO ADD @.NEWLOGIN INTO THE APPROPRIATE SERVER ROLES
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'EXECUTE
> [MASTER].[DBO].[SP_ADDSRVROLEMEMBER] ' + '[' +
> RTRIM(@.NEWLOGIN) + ']' +
> ',[' + RTRIM(A.SERVERROLE) + ']' AS RIGHTS_TEXT
> FROM #TMPSRVROLES A
> WHERE A.MEMBERNAME = @.OLDLOGIN
> --
> -- DROP SERVER ROLE TABLE
> --
> DROP TABLE #TMPSRVROLES
> -- END CALLOUT E
> -- BEGIN CALLOUT F
> -- GRANT USER PERMISSIONS TO OBJECTS AND STATEMENTS
> ----
--
> -- CREATE TEMPORARY TABLE TO HOLD INFORMATION ABOUT OBJECTS PERMISSIONS
> --
> CREATE TABLE #TMPPROTECT (
> OWNER VARCHAR(100),
> OBJECT VARCHAR(100),
> GRANTEE VARCHAR(100),
> GRANTOR VARCHAR(100),
> PROTECTTYPE CHAR(10),
> ACTION VARCHAR(20),
> COLUMNX VARCHAR(100))
> OPEN ALLDATABASES
> SET @.CMD = 'SELECT @.OLDUSER=NAME FROM [' + @.DB +
> '].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM
> [MASTER].[DBO].[SYSLOGINS] WHERE LOGINNAME = ' +
> CHAR(39) + @.OLDLOGIN + CHAR(39) + ')'
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --
> -- INITIALIZE @.OLDUSER VARIABLE
> --
> SET @.OLDUSER = ''
> --
> --GENERATE COMMAND TO SET @.OLDUSER TO THE USER NAME IN DATABASE
> -- IF @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
> --
> SET @.CMD = 'SELECT @.OLDUSER=NAME FROM [' + @.DB +
> '].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM
> [MASTER].[DBO].[SYSLOGINS] WHERE LOGINNAME = ' +
> CHAR(39) + @.OLDLOGIN + CHAR(39) + ')'
> --
> -- EXECUTE COMMAND TO SET @.OLDUSER TO THE USER NAME IN DATABASE
> -- IF @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
> --
> EXEC [MASTER].[DBO].[SP_EXECUTESQL] @.CMD,N'@.OLDUSER CHAR(200)
> OUTPUT',@.OLDUSER OUT
> --
> -- IF @.OLDUSER IS NOT BLANK THEN @.OLDLOGIN HAS ACCESS TO CURRENT DATABASE
> --
> IF @.OLDUSER <> ''
> BEGIN
> --
> -- GENERATE COMMAND TO GET OBJECT PERMISSIONS FOR CURRENT DATABASE
> --
> SET @.CMD = '[' + @.DB + '].[DBO].[SP_HELPROTECT]'
> --
> -- GET OBJECT PERMISSIONS INTO TEMPORARY TABLE
> --
> INSERT INTO #TMPPROTECT EXEC (@.CMD)
> --
> -- DETERMINE IF THERE ARE ANY OBJECT PERMISSIONS FOR @.OLDUSER
> --
> SELECT @.CNT = COUNT(*) FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER
> IF @.CNT > 0
> --
> -- SWITCH TO THE APPROPRIATE DATABASE
> --
> INSERT INTO #TMP_LOGIN_RIGHTS SELECT 'USE [' + @.DB + ']'
> --
> -- GENERATE COMMANDS TO GRANT OBJECTS PERMISSIONS FOR REFERENCES, SELECT,
> UPDATE TO @.NEWUSER
> --
> INSERT INTO #TMP_LOGIN_RIGHTS
> SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
> THEN
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> '] TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
> OPTION'
> ELSE
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> '] TO [' + RTRIM(@.NEWUSER) + ']'
> END AS RIGHTS_TEXT
> FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT <> '.'
> AND COLUMNX = '(ALL+NEW)'
> --
> -- GRANT COLUMN PERMISSION ON OBJECTS
> --
> INSERT INTO #TMP_LOGIN_RIGHTS
> SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
> THEN
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> ']([' + COLUMNX + '])' +
> ' TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
> OPTION'
> ELSE
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> ']([' + COLUMNX + '])' +
> ' TO [' + RTRIM(@.NEWUSER) + ']'
> END AS RIGHTS_TEXT
> FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER
> AND OBJECT <> '.' AND COLUMNX <> '(ALL+NEW)'
> AND COLUMNX <> '.'
> --
> -- GRANT INSERT, DELETE, AND EXECUTE PERMISSION ON OBJECTS
> --
> INSERT INTO #TMP_LOGIN_RIGHTS
> SELECT CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
> THEN
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> '] TO [' + RTRIM(@.NEWUSER) + ']' +' WITH GRANT
> OPTION'
> ELSE
> 'GRANT ' +
> ACTION +' ON [' +
> @.DB + '].[' + OWNER + '].[' + OBJECT +
> '] TO [' + RTRIM(@.NEWUSER) + ']'
> END AS RIGHTS_TEXT
> FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT <> '.' AND
> ACTION IN ('INSERT', 'DELETE', 'EXECUTE') AND COLUMNX = '.'
> --
> -- GRANT STATEMENT PERMISSIONS
> --
> --
> INSERT INTO #TMP_LOGIN_RIGHTS
> SELECT 'GRANT ' +
> ACTION +
> ' TO [' + RTRIM(@.NEWUSER) + ']'
> AS RIGHTS_TEXT
> FROM #TMPPROTECT WHERE GRANTEE = @.OLDUSER AND OBJECT = '.'
>
> --
> -- REMOVE RECORDS FOR TEMPORARY TABLE IN PREPARATION FOR THE NEXT DATABASE
> TO BE PROCESSES
> --
> TRUNCATE TABLE #TMPPROTECT
> END
> --
> -- GET NEXT DATABASE TO PROCESS
> --
> FETCH NEXT FROM ALLDATABASES INTO @.DB
> END -- WHILE (@.@.FETCH_STATUS = 0)
> --
> -- CLOSE AND DEALLOCATE DATABASE LIST CURSOR
> --
> CLOSE ALLDATABASES
> DEALLOCATE ALLDATABASES
> --
> -- DROP TEMPORARY TABLE THAT HELD OBJECT PERMISSIONS
> --
> DROP TABLE #TMPPROTECT
> -- END CALLOUT F
> -- BEGIN CALLOUT G
> -- PROCESS ALL GENERATED COMMANDS ONE AT A TIME
> ----
--
> --
> -- GET ALL THE GENERATED COMMANDS
> --
> DECLARE COMMANDS CURSOR FOR
> SELECT * FROM #TMP_LOGIN_RIGHTS
> OPEN COMMANDS
> FETCH NEXT FROM COMMANDS INTO @.CMD
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --
> -- PRINT COMMAND TO BE PROCESSED
> --
> PRINT @.CMD
> --
> -- UNCOMMENT IF YOU WANT THE STORED PROCEDURE TO EXECUTE THE COMMANDS TO
> ADD
> THE PERMISSIONS
> --
> --EXEC (@.CMD)
> --
> -- GET NEXT COMMAND
> --
> FETCH NEXT FROM COMMANDS INTO @.CMD
> END -- WHILE (@.@.FETCH_STATUS = 0)
> --
> -- CLOSE AND DEALLOCATE COMMAND CURSOR
> --
> CLOSE COMMANDS
> DEALLOCATE COMMANDS
> --
> -- DROP TABLE THAT HELD THE GENERATED RIGHTS THAT WHERE GRANTED TO
> @.NEWLOGIN
> --
> DROP TABLE #TMP_LOGIN_RIGHTS
> -- END CALLOUT G
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "Neil Meyer" wrote:
>

List of Permissions

Does anyone know how to get a listing of each user in a database and the
permissions each user has? I found sp_helplogins and that gives me some
information but I'm looking to get a listing of permissions down to the
object level.
Thanks!
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Rachael,
permissions are held in sysprotects and sp_helpprotect can be used to list
them. Note that this applies to explicitly granted permissions and most
permissions in SQL Server are implied, by virtue of being in a role or an
object owner.
HTH,
Paul Ibison
|||Thank you! I'll give it a try.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi,
Join the syspermissions with spt_values table of master database to get the
details.
or else
use the system procedure SP_HELPROTECT to get the detailed information about
the object permissions.
sp_helprotect null,user_name (replace user_name with your user name)
Thanks
Hari
MCDBA
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:OlpU2LyTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to get a listing of each user in a database and the
> permissions each user has? I found sp_helplogins and that gives me some
> information but I'm looking to get a listing of permissions down to the
> object level.
> Thanks!
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

List of Permissions

Does anyone know how to get a listing of each user in a database and the
permissions each user has? I found sp_helplogins and that gives me some
information but I'm looking to get a listing of permissions down to the
object level.
Thanks!
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Rachael,
permissions are held in sysprotects and sp_helpprotect can be used to list
them. Note that this applies to explicitly granted permissions and most
permissions in SQL Server are implied, by virtue of being in a role or an
object owner.
HTH,
Paul Ibison|||Thank you! I'll give it a try.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi,
Join the syspermissions with spt_values table of master database to get the
details.
or else
use the system procedure SP_HELPROTECT to get the detailed information about
the object permissions.
sp_helprotect null,user_name (replace user_name with your user name)
Thanks
Hari
MCDBA
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:OlpU2LyTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to get a listing of each user in a database and the
> permissions each user has? I found sp_helplogins and that gives me some
> information but I'm looking to get a listing of permissions down to the
> object level.
> Thanks!
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

List of Permissions

Does anyone know how to get a listing of each user in a database and the
permissions each user has? I found sp_helplogins and that gives me some
information but I'm looking to get a listing of permissions down to the
object level.
Thanks!
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Rachael,
permissions are held in sysprotects and sp_helpprotect can be used to list
them. Note that this applies to explicitly granted permissions and most
permissions in SQL Server are implied, by virtue of being in a role or an
object owner.
HTH,
Paul Ibison|||Hi,
Join the syspermissions with spt_values table of master database to get the
details.
or else
use the system procedure SP_HELPROTECT to get the detailed information about
the object permissions.
sp_helprotect null,user_name (replace user_name with your user name)
Thanks
Hari
MCDBA
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:OlpU2LyTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to get a listing of each user in a database and the
> permissions each user has? I found sp_helplogins and that gives me some
> information but I'm looking to get a listing of permissions down to the
> object level.
> Thanks!
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

List of all folder permissions

Is there a way to get a list of all folder permissions per folder? We
are in the process of cleaning up two Reporting Services boxes that
have gotten way out of control, including permissions. Part of this
process is assigning users to roles, rather than individual permissions
on folders. But to do this, I need to see who is assigned to what
folder (and there are many). Any ideas?I think RS Scripter will help you in this.
http://www.sqldbatips.com/samples/code/RSScripter/readme.htm#_introduction
Someone wrote an OLAP documenter as a Word macro. When opening the word doc
you could point it at a server and it would document all the cubes. Same
thing would be nice for RS.
Steve MunLeeuw
"madrigal" <nmadrigal@.att.net> wrote in message
news:1160661235.716193.208570@.i3g2000cwc.googlegroups.com...
> Is there a way to get a list of all folder permissions per folder? We
> are in the process of cleaning up two Reporting Services boxes that
> have gotten way out of control, including permissions. Part of this
> process is assigning users to roles, rather than individual permissions
> on folders. But to do this, I need to see who is assigned to what
> folder (and there are many). Any ideas?
>