listingreports that user has access

How can I get the list of reports that a user has only view reports access ?
the user is in windows server 2003 active directory.
reporting services 2000 is being used.
within the intranet there is a portal.
in the portal I want to list the available reports to the user
that he can access. I do not want to display all the reports to him and let
him choose. I want him to be able to see only the reports he is allowed to
see?
how can I do this using asp.net 1.1
thanx
rifatis it possible or not?
can someone answer please ?
thanx
rifatsql

Listing Size of all user Databases in SQL2005

Can anyone please tell me of a way of generating a list of all user databases and their size with one command or view?

Thanks,

Refer to Books Online, Topic: sys.database_files

This will return the size as the number of 8K pages, so do the math in your query.

|||sp_helpdb will generate info about the dbname and size|||

select * from sys.master_files

Will also give you useful information.

Listing Size of all user Databases in SQL2005

Can anyone please tell me of a way of generating a list of all user databases and their size with one command or view?

Thanks,

Refer to Books Online, Topic: sys.database_files

This will return the size as the number of 8K pages, so do the math in your query.

|||sp_helpdb will generate info about the dbname and size|||

select * from sys.master_files

Will also give you useful information.

listing non system databases

I know sp_databases can list all the databases, but how can i only list
non-system databases? I just want to get a list of user databasesYou can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
system table to the the list of all databases. You can avoid the master,
model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
clause to get the list of non-system databases.
Anith|||Hi
If you profile SQL Management Studio in SQL 2005 to see how it determines a
system database, you will see that it queries sysdatabases and determines
that the are system database by their name.
John
"Brian Henry" <nospam@.nospam.com> wrote in message
news:u5cw%23c7CGHA.3064@.TK2MSFTNGP10.phx.gbl...
>I know sp_databases can list all the databases, but how can i only list
>non-system databases? I just want to get a list of user databases
>|||> You can query the INFORMATION_SCHEMA.SCHEMATA view
Note that this view has a different meaning in 2005, where it returns the sc
hemas in the current
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u734707CGHA.2704@.TK2MSFTNGP15.phx.gbl...
> You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases sys
tem table to the the
> list of all databases. You can avoid the master, model, msdb, mssqlweb, te
mpdb, Pubs & Northwind
> databases in the WHERE clause to get the list of non-system databases.
> --
> Anith
>|||Yes, thanks. In 2005, use sys.databases instead.
Anith

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 users?

How can i get a list of user that have access to all my databases?
i know how to view them but i need to output them to a text file or excel.right click on Logins then select Export List...|||Hi

Try this code

use somedatabase
go
select * from sysusers

List of users and user rights

Does anyone have a query that will list all the users and user rights for a sqlserver database.

Thanks so much!sp_helpuser will list all the users.
sp_helplogins gives all the login users.
these procedures might help you

List of tables used in query

Hi all,
Is there any way to get the list of the tables used
in the select statment ?
I want to create an application in which when user fires
any query i want to store tables used in that query in
other table.
for e.g if user execute
--
select * from table mytablea join mytableb on id = id
--
then i want to get mytablea and mytableb
as my list of tables.
thanks..
--
Thanks & Regards
MalkeshHi
Is it just simple a SELECT statement or a stored procedures,views as well?
"Malkesh" <Malkesh@.discussions.microsoft.com> wrote in message
news:0E9B0793-3202-4FBB-BC3B-C00F43829B09@.microsoft.com...
> Hi all,
> Is there any way to get the list of the tables used
> in the select statment ?
> I want to create an application in which when user fires
> any query i want to store tables used in that query in
> other table.
> for e.g if user execute
> --
> select * from table mytablea join mytableb on id = id
> --
> then i want to get mytablea and mytableb
> as my list of tables.
> thanks..
> --
> Thanks & Regards
> Malkesh|||Hi,
It's just a select statment, but may involve complex joins, subqueries etc..
.
--
Thanks & Regards
Malkesh
"Uri Dimant" wrote:

> Hi
> Is it just simple a SELECT statement or a stored procedures,views as well?
>
>
> "Malkesh" <Malkesh@.discussions.microsoft.com> wrote in message
> news:0E9B0793-3202-4FBB-BC3B-C00F43829B09@.microsoft.com...
>
>

List of synchs?

Which query gives me the no of synchs in Merge replication? If a user is
synchronizing 10 times I need the information of all the 10 synchs.
I am using the following query,
SELECT a.subscriber_db
,runstatus
,start_time
,end_time
,duration [Synch Duration]
FROM dbo.MSmerge_sessions s
LEFT OUTER JOIN MSmerge_agents a
ON a.id=s.agent_id
This query is not giving me consistent result. Every time I run this query
I get some of the records missing.
Can someone put some light?
This should work. Is it possible that there are no rows synchronized during
these missing syncs?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
news:07DC40EB-9386-4A41-82B8-027D80FDA4F9@.microsoft.com...
> Which query gives me the no of synchs in Merge replication? If a user is
> synchronizing 10 times I need the information of all the 10 synchs.
> I am using the following query,
> SELECT a.subscriber_db
> ,runstatus
> ,start_time
> ,end_time
> ,duration [Synch Duration]
> FROM dbo.MSmerge_sessions s
> LEFT OUTER JOIN MSmerge_agents a
> ON a.id=s.agent_id
> This query is not giving me consistent result. Every time I run this
> query
> I get some of the records missing.
> Can someone put some light?
>

List of stored procedures with permission for executing for user

I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?

Thanks for tips

If you want to get the list of stored procedures , on which specific database user ('XY') has EXECUTE permission explicitly granted , consider the following query:

Code Snippet

SELECT [name]

FROM sys.objects obj

INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id

WHERE obj.[type] = 'P' -- stored procedure

AND dp.permission_name = 'EXECUTE'

AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT

AND dp.grantee_principal_id =

(SELECT principal_id FROM sys.database_principals WHERE [name] = 'XY')

But you should be aware that here you deal only with explicitly granted permissions, not effective permissions of the user.|||

thanks a lot|||

is there any way to do this with sql server 2000?

List of stored procedures with permission for executing for user

I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?

Thanks for tips

If you want to get the list of stored procedures , on which specific database user ('XY') has EXECUTE permission explicitly granted , consider the following query:

Code Snippet

SELECT [name]

FROM sys.objects obj

INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id

WHERE obj.[type] = 'P' -- stored procedure

AND dp.permission_name = 'EXECUTE'

AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT

AND dp.grantee_principal_id =

(SELECT principal_id FROM sys.database_principals WHERE [name] = 'XY')

But you should be aware that here you deal only with explicitly granted permissions, not effective permissions of the user.|||thanks a lot|||

is there any way to do this with sql server 2000?

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 Databases

I am writing an Active Server Page application that will get all of the
databases that a user can access. Is there a way I can do this with Active
Server Pages? I also will provide the users with the option to change
records, table structure, etc with this application.
I will also be developing an asp.net (C#) project that will do the same
thing.
If anyone can provide code snippets or anything that would be great.
Thanks,
Rick Langschultz
Web Developer/IT Manager
Rick Langschultz wrote:
> I am writing an Active Server Page application that will get all of
> the databases that a user can access. Is there a way I can do this
> with Active Server Pages? I also will provide the users with the
> option to change records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the
> same thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
What database? Should we assume that it's SQL Server given the inclusion of
the sqlserver groups in your crosspost?
Please don't make us guess. We need to know the type and version of rdbms
you are using.
You left out the dotnet groups that could have helped you with the .Net
code you need (I'm responding from the .asp group which is for classic ASP
questions)
For starters, do a search at www.aspfaq.com. Here's one of the articles you
will find:
http://www.aspfaq.com/show.asp?id=2456
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
|||the easiest way is:
select name from master.dbo.sysdatabases
Leo
"Rick Langschultz" wrote:

> I am writing an Active Server Page application that will get all of the
> databases that a user can access. Is there a way I can do this with Active
> Server Pages? I also will provide the users with the option to change
> records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the same
> thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
>
>

List of Databases

I am writing an Active Server Page application that will get all of the
databases that a user can access. Is there a way I can do this with Active
Server Pages? I also will provide the users with the option to change
records, table structure, etc with this application.
I will also be developing an asp.net (C#) project that will do the same
thing.
If anyone can provide code snippets or anything that would be great.
Thanks,
Rick Langschultz
Web Developer/IT Manager
Rick Langschultz wrote:
> I am writing an Active Server Page application that will get all of
> the databases that a user can access. Is there a way I can do this
> with Active Server Pages? I also will provide the users with the
> option to change records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the
> same thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
What database? Should we assume that it's SQL Server given the inclusion of
the sqlserver groups in your crosspost?
Please don't make us guess. We need to know the type and version of rdbms
you are using.
You left out the dotnet groups that could have helped you with the .Net
code you need (I'm responding from the .asp group which is for classic ASP
questions)
For starters, do a search at www.aspfaq.com. Here's one of the articles you
will find:
http://www.aspfaq.com/show.asp?id=2456
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
|||the easiest way is:
select name from master.dbo.sysdatabases
Leo
"Rick Langschultz" wrote:

> I am writing an Active Server Page application that will get all of the
> databases that a user can access. Is there a way I can do this with Active
> Server Pages? I also will provide the users with the option to change
> records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the same
> thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
>
>

List of Databases

I am writing an Active Server Page application that will get all of the
databases that a user can access. Is there a way I can do this with Active
Server Pages? I also will provide the users with the option to change
records, table structure, etc with this application.
I will also be developing an asp.net (C#) project that will do the same
thing.
If anyone can provide code snippets or anything that would be great.
Thanks,
Rick Langschultz
Web Developer/IT ManagerRick Langschultz wrote:
> I am writing an Active Server Page application that will get all of
> the databases that a user can access. Is there a way I can do this
> with Active Server Pages? I also will provide the users with the
> option to change records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the
> same thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
What database? Should we assume that it's SQL Server given the inclusion of
the sqlserver groups in your crosspost?
Please don't make us guess. We need to know the type and version of rdbms
you are using.
You left out the dotnet groups that could have helped you with the .Net
code you need (I'm responding from the .asp group which is for classic ASP
questions)
For starters, do a search at www.aspfaq.com. Here's one of the articles you
will find:
http://www.aspfaq.com/show.asp?id=2456
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||the easiest way is:
select name from master.dbo.sysdatabases
Leo
"Rick Langschultz" wrote:

> I am writing an Active Server Page application that will get all of the
> databases that a user can access. Is there a way I can do this with Active
> Server Pages? I also will provide the users with the option to change
> records, table structure, etc with this application.
> I will also be developing an asp.net (C#) project that will do the same
> thing.
> If anyone can provide code snippets or anything that would be great.
> Thanks,
> Rick Langschultz
> Web Developer/IT Manager
>
>

List of Database Users

In certain applications, it would be useful to be able to display to an
'ordinary' user, a list of other accessors of a particular database. Any
ideas how this can be done?
--
John AustinJohn
Lookup sysusers system table in the BOL
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:1B2F5FAB-E1E6-46DE-9A63-2F00234DD581@.microsoft.com...
> In certain applications, it would be useful to be able to display to an
> 'ordinary' user, a list of other accessors of a particular database. Any
> ideas how this can be done?
> --
> John Austin|||To add on to Uri's response, other methods include executing sp_helpuser or
using DMO.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:1B2F5FAB-E1E6-46DE-9A63-2F00234DD581@.microsoft.com...
> In certain applications, it would be useful to be able to display to an
> 'ordinary' user, a list of other accessors of a particular database. Any
> ideas how this can be done?
> --
> John Austin|||sysusers and sp_helpuser seem to show all the users set up for the database
-
I really want to know about those with an open connection such as can be see
n
in Enterprise Manager in 'Management', 'Current Activity', 'Process Info'
Thanks
--
John Austin
"Dan Guzman" wrote:

> To add on to Uri's response, other methods include executing sp_helpuser o
r
> using DMO.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Austin" <John.Austin@.nospam.nospam> wrote in message
> news:1B2F5FAB-E1E6-46DE-9A63-2F00234DD581@.microsoft.com...
>
>|||sp_who, for instance
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:D8EE51EF-3662-4ED7-A311-F6A26EE6F08B@.microsoft.com...
> sysusers and sp_helpuser seem to show all the users set up for the databas
e -
> I really want to know about those with an open connection such as can be s
een
> in Enterprise Manager in 'Management', 'Current Activity', 'Process Info'
> Thanks
> --
> John Austin
>
> "Dan Guzman" wrote:
>

List of al lindexes and their properties for a db

sp_helpindex gives information for a single table. Is there a way of getting the same information for all user tables in a database?You may want to use the sys.indexes catalog view.

List database users & login

In SQL Server 2000, we can expand the console to "Database
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peter
Hello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegrou ps.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegrou ps.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>