http://www.mssqltips.com/tip.asp?tip=1252
-------------------------------------------
Problem
Managing database security should be part of every DBAs job. With SQL Server you have the ability to use either SQL Server and Windows or Windows only security. The advantage of using Windows security for your SQL Servers is that you can take advantage of the Windows security model and security policies that have been setup on your domain. Another advantage is that you can manage security at the domain level instead of at the SQL Server instance level.
In addition you can use use Windows groups to manage the security buckets. Based on the groups that are setup you can put specific Windows users in these groups and then give SQL Server access to this Windows group instead of having to create logins for every single windows user.
The disadvantage to this is that the specific people within these groups is masked at the SQL Server level. In your logins you can see the groups that have access, but you have no idea what users are in what groups. So how can you get this information from within SQL Server?
Solution
SQL Server offers some insight into this issue with the xp_logininfo extended stored procedure. This stored procedure is part of both SQL Server 2000 and SQL Server 2005. This xp takes the following parameters:
@acctname - the windows account name or group
@option - information to display
'all' - display information for all permission paths
'members' - display list of members in a group
@privelege - this is an output variable from this command and returns 'admin', 'user' or 'null
Following is T-SQL code that loops through your logins and wherever there is a Windows Group the xp_logininfo XP is called to return information about the Windows group.
SQL Server 2000
For this example we are querying from the syslogins table where the isntgroup = 1 and status = 10.
DECLARE @LoginName sysname
DECLARE @sql NVARCHAR (2000)
BEGIN
DECLARE cur_Loginfetch CURSOR FOR
SELECT [name] FROM master.dbo.syslogins WHERE isntgroup = 1 AND status = 10
OPEN cur_Loginfetch
FETCH NEXT FROM cur_Loginfetch INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC xp_logininfo @LoginName , 'members'
FETCH NEXT FROM cur_Loginfetch INTO @LoginName
END
CLOSE cur_Loginfetch
DEALLOCATE cur_Loginfetch
RETURN
END
----------------
SQL Server 2005
For this example we are querying from the sys.server_principals catalog view where type = 'G'.
DECLARE @LoginName sysname
DECLARE @sql NVARCHAR (2000)
BEGIN
DECLARE cur_Loginfetch CURSOR FOR
SELECT [name] FROM master.sys.server_principals WHERE TYPE = 'G'
OPEN cur_Loginfetch
FETCH NEXT FROM cur_Loginfetch INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC xp_logininfo @LoginName , 'members'
FETCH NEXT FROM cur_Loginfetch INTO @LoginName
END
CLOSE cur_Loginfetch
DEALLOCATE cur_Loginfetch
RETURN
END
donderdag 27 november 2008
Check Again --SQL Server 2000
USE master
GO
IF OBJECT_ID('sp_ListPermissions') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID
('sp_ListPermissions'),'IsProcedure')=1
DROP PROCEDURE sp_ListPermissions
GO
CREATE PROCEDURE sp_ListPermissions
@object nvarchar(515) = NULL,
@column sysname = NULL
AS
/*
Procedure to list a uses effective permissions
Author : Itzik Ben−Gan
*/
SET NOCOUNT ON
DECLARE @permissions int
DECLARE @objectid int
SET @objectid = object_id(@object)
--Check input
IF @object IS NOT NULL AND @objectid IS NULL
BEGIN
RAISERROR('Invalid object %s supplied.', 16, 1, @object)
RETURN 1
END
IF @objectid IS NOT NULL
BEGIN
PRINT 'Object permissions on: ' + @object + ' for
login: ' + SUSER_SNAME()
+ ', user: ' + USER_NAME() + '...'
IF @column IS NOT NULL
BEGIN
SET @permissions = permissions(@objectid, @column)
PRINT 'On column: ' + @column + '...'
IF @permissions & 1 = 1 PRINT 'SELECT'
IF @permissions & 2 = 2 PRINT 'UPDATE'
IF @permissions & 4 = 4 PRINT 'REFERENCES'
END -- @column IS NOT NULL
ELSE -- @column IS NULL
BEGIN
SET @permissions = permissions(@objectid)
IF @permissions & 1 = 1 PRINT 'SELECT ALL'
IF @permissions & 2 = 2 PRINT 'UPDATE ALL'
IF @permissions & 4 = 4 PRINT 'REFERENCES ALL'
IF @permissions & 8 = 8 PRINT 'INSERT'
IF @permissions & 16 = 16 PRINT 'DELETE'
IF @permissions & 32 = 32 PRINT 'EXECUTE'
IF @permissions & 4096 = 4096 PRINT 'SELECT ANY (at least one column)'
IF @permissions & 8192 = 8192 PRINT 'UPDATE ANY (at least one column)'
IF @permissions & 16384 = 16384 PRINT 'REFERENCES ANY(at least one column)'
END -- @column IS NULL
END -- @objectid IS NOT NULL
ELSE -- @objectid IS NULL
BEGIN
SET @permissions = permissions()
PRINT 'Statement permissions for login: ' + SUSER_SNAME() + ', user: ' + USER_NAME() + '...'
IF @permissions & 1 = 1 PRINT 'CREATE DATABASE'
IF @permissions & 2 = 2 PRINT 'CREATE TABLE'
IF @permissions & 4 = 4 PRINT 'CREATE PROCEDURE'
IF @permissions & 8 = 8 PRINT 'CREATE VIEW'
IF @permissions & 16 = 16 PRINT 'CREATE RULE'
IF @permissions & 32 = 32 PRINT 'CREATE DEFAULT'
IF @permissions & 64 = 64 PRINT 'BACKUP DATABASE'
IF @permissions & 128 = 128 PRINT 'BACKUP LOG'
--IF @permissions & 256 = 256 PRINT 'Reserved'
END -- @objectid IS NULL
GO
grant exec on sp_ListPermissions to public
GO
--================================================
declare @chvDBName varchar(50)
set @chvDBName = db_name()
SELECT
'Database' = @chvDBName,
'UserOrRoleName' = sysusers.name,
'GrantType' = case protecttype
when 204 then 'RANT_W_GRANT'
when 205 then 'GRANT'
when 206 then 'REVOKE' end,
Permission = CASE action
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 26 THEN 'REFERENCE'
WHEN 224 THEN 'EXECUTE'
ELSE 'Unknown'
END
, 'ObjectName' = sysobjects.name
, 'ObjectType' = case when sysobjects.xtype = 'U' then 'Table'
when sysobjects.xtype = 'V' then 'View'
when sysobjects.xtype = 'P' then 'Procedure'
else 'Other'
end,
'Is_Role' = case when issqlrole = 1 then 'Yes' else 'No' end
FROM sysprotects, sysobjects, sysusers
WHERE sysobjects.id = sysprotects.id
AND sysprotects.action IN (193, 195, 196, 197, 224, 26)
AND sysprotects.uid = sysusers.uid
AND sysobjects.name not like 'dt[_]%'
AND sysobjects.name not like 'dt%'
AND sysobjects.name not like 'sel[_]%'
AND sysobjects.name not like 'sp_ins[_]%'
AND sysobjects.name not like 'sp_upd[_]%'
AND sysobjects.name not like 'sp_sel[_]%'
AND sysobjects.name not like 'sp_cft[_]%'
AND sysobjects.name not like 'ctsv[_]%'
AND sysobjects.name not like 'tsvw[_]%'
AND sysusers.name not like 'MSmerge[_]%'
AND sysobjects.xtype <> 'S'
AND sysobjects.name not in ('MSsubscription_agents', 'sysmergearticles', 'sysconstraints', 'syssegments')
ORDER BY sysusers.name, sysobjects.xtype, sysobjects.name
GO
--**************************************
--
-- Name: SQL Server Login Info
-- Description:Retrieve the SQL Server l
-- ogin configuration, trusted logins etc.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/vb/scripts/Sh
-- owCode.asp?txtCodeId=236&lngWId=5--for details.--**************************************
--
USE master
go
IF object_id('sp_Mylogininfo') IS NOT NULL
DROP PROCEDURE sp_Mylogininfo
go
CREATE PROCEDURE sp_Mylogininfo
(
@loginmode varchar(30) = NULL OUTPUT,
@integrated varchar(30) = NULL OUTPUT,
@showdetails bit = 0
)
as
/********************************************************************************/
/* Created BY : Umachandar Jayachandran (UC) */
/* Created ON : 20 October 1996 */
/* Description: This stored PROCEDURE can be used to obtain information */
/* about a SQL Server login. IF executed WITH no parameters*/
/* the stored PROCEDURE will report IF the CURRENT USER IS */
/* connected through standard OR integrated/mixed type */
/* login. The output can also be obtained through variables*/
/* TO make it easy to USE FROM other SPs. The showdetails */
/* flag will display the CURRENT NT logins AND groups which*/
/* have been granted access TO SQL Server. */
/********************************************************************************/
/* Resources : http://www.umachandar.com/resources.htm */
/********************************************************************************/
SET nocount ON
SET ansi_defaults OFF
DECLARE @account varchar(30), @nt_domain varchar(30), @nt_username varchar(30),
@privilege varchar(30), @showresults bit
IF @loginmode IS NULL or @integrated IS NULL
SELECT @showresults = 1
CREATE TABLE #loginconfig (name varchar(30), config_value varchar(30) null)
INSERT #loginconfig EXEC master..xp_loginconfig
SELECT @loginmode = config_value FROM #loginconfig WHERE name = 'login mode'
IF @loginmode = 'standard'
begin
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
CREATE TABLE #logininfo (account varchar(60), type varchar(30),
privilege varchar(30) null, mapped_login varchar(60) null,
permission_path varchar(255) null)
INSERT #logininfo EXEC master..xp_logininfo
DECLARE accounts insensitive CURSOR FOR
SELECT account FROM #logininfo WHERE charindex('SYSTEM', account) = 0
OPEN accounts
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM accounts INTO @account
IF @@fetch_status < 0 BREAK
INSERT #logininfo EXEC master..xp_logininfo @account, 'members'
end
DEALLOCATE accounts
SELECT @nt_domain = nt_domain, @nt_username = nt_username FROM sysprocesses
WHERE spid = @@spid
-- First check for sa
IF suser_id() = 1
begin
-- Next CHECK FOR NT USER privilege
IF exists( SELECT privilege FROM #logininfo
WHERE account = @nt_domain + '\' + @nt_username
AND privilege = 'admin')
SELECT @integrated = 'Yes'
ELSE
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
-- Second check for users
SELECT @integrated = CASE WHEN @nt_username = suser_name() THEN 'Yes' ELSE 'No' END
SHOW_RESULTS_LABEL:
IF @showresults = 1
begin
SELECT @loginmode AS "Server Login Mode",
@integrated AS "Integrated"
end
IF @showdetails = 1
begin
PRINT ''
PRINT 'Server Security Configuration'
SELECT * FROM #loginconfig
PRINT ''
PRINT 'Server Security Details'
SELECT * FROM #logininfo
end
go
GRANT EXECUTE on sp_Mylogininfo TO PUBLIC
go
-- Usage Examples:
PRINT 'With no parameters...'
EXEC sp_Mylogininfo
PRINT ''
go
PRINT 'With show details option...'
EXEC sp_Mylogininfo @showdetails = 1
PRINT ''
go
PRINT 'With output parameters...'
DECLARE @loginmode varchar(30), @integrated varchar(30)
SELECT @loginmode = '', @integrated = ''
EXEC sp_Mylogininfo @loginmode out, @integrated out
SELECT @loginmode AS LoginMode, @integrated as Integrated
GO
IF OBJECT_ID('sp_ListPermissions') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID
('sp_ListPermissions'),'IsProcedure')=1
DROP PROCEDURE sp_ListPermissions
GO
CREATE PROCEDURE sp_ListPermissions
@object nvarchar(515) = NULL,
@column sysname = NULL
AS
/*
Procedure to list a uses effective permissions
Author : Itzik Ben−Gan
*/
SET NOCOUNT ON
DECLARE @permissions int
DECLARE @objectid int
SET @objectid = object_id(@object)
--Check input
IF @object IS NOT NULL AND @objectid IS NULL
BEGIN
RAISERROR('Invalid object %s supplied.', 16, 1, @object)
RETURN 1
END
IF @objectid IS NOT NULL
BEGIN
PRINT 'Object permissions on: ' + @object + ' for
login: ' + SUSER_SNAME()
+ ', user: ' + USER_NAME() + '...'
IF @column IS NOT NULL
BEGIN
SET @permissions = permissions(@objectid, @column)
PRINT 'On column: ' + @column + '...'
IF @permissions & 1 = 1 PRINT 'SELECT'
IF @permissions & 2 = 2 PRINT 'UPDATE'
IF @permissions & 4 = 4 PRINT 'REFERENCES'
END -- @column IS NOT NULL
ELSE -- @column IS NULL
BEGIN
SET @permissions = permissions(@objectid)
IF @permissions & 1 = 1 PRINT 'SELECT ALL'
IF @permissions & 2 = 2 PRINT 'UPDATE ALL'
IF @permissions & 4 = 4 PRINT 'REFERENCES ALL'
IF @permissions & 8 = 8 PRINT 'INSERT'
IF @permissions & 16 = 16 PRINT 'DELETE'
IF @permissions & 32 = 32 PRINT 'EXECUTE'
IF @permissions & 4096 = 4096 PRINT 'SELECT ANY (at least one column)'
IF @permissions & 8192 = 8192 PRINT 'UPDATE ANY (at least one column)'
IF @permissions & 16384 = 16384 PRINT 'REFERENCES ANY(at least one column)'
END -- @column IS NULL
END -- @objectid IS NOT NULL
ELSE -- @objectid IS NULL
BEGIN
SET @permissions = permissions()
PRINT 'Statement permissions for login: ' + SUSER_SNAME() + ', user: ' + USER_NAME() + '...'
IF @permissions & 1 = 1 PRINT 'CREATE DATABASE'
IF @permissions & 2 = 2 PRINT 'CREATE TABLE'
IF @permissions & 4 = 4 PRINT 'CREATE PROCEDURE'
IF @permissions & 8 = 8 PRINT 'CREATE VIEW'
IF @permissions & 16 = 16 PRINT 'CREATE RULE'
IF @permissions & 32 = 32 PRINT 'CREATE DEFAULT'
IF @permissions & 64 = 64 PRINT 'BACKUP DATABASE'
IF @permissions & 128 = 128 PRINT 'BACKUP LOG'
--IF @permissions & 256 = 256 PRINT 'Reserved'
END -- @objectid IS NULL
GO
grant exec on sp_ListPermissions to public
GO
--================================================
declare @chvDBName varchar(50)
set @chvDBName = db_name()
SELECT
'Database' = @chvDBName,
'UserOrRoleName' = sysusers.name,
'GrantType' = case protecttype
when 204 then 'RANT_W_GRANT'
when 205 then 'GRANT'
when 206 then 'REVOKE' end,
Permission = CASE action
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 26 THEN 'REFERENCE'
WHEN 224 THEN 'EXECUTE'
ELSE 'Unknown'
END
, 'ObjectName' = sysobjects.name
, 'ObjectType' = case when sysobjects.xtype = 'U' then 'Table'
when sysobjects.xtype = 'V' then 'View'
when sysobjects.xtype = 'P' then 'Procedure'
else 'Other'
end,
'Is_Role' = case when issqlrole = 1 then 'Yes' else 'No' end
FROM sysprotects, sysobjects, sysusers
WHERE sysobjects.id = sysprotects.id
AND sysprotects.action IN (193, 195, 196, 197, 224, 26)
AND sysprotects.uid = sysusers.uid
AND sysobjects.name not like 'dt[_]%'
AND sysobjects.name not like 'dt%'
AND sysobjects.name not like 'sel[_]%'
AND sysobjects.name not like 'sp_ins[_]%'
AND sysobjects.name not like 'sp_upd[_]%'
AND sysobjects.name not like 'sp_sel[_]%'
AND sysobjects.name not like 'sp_cft[_]%'
AND sysobjects.name not like 'ctsv[_]%'
AND sysobjects.name not like 'tsvw[_]%'
AND sysusers.name not like 'MSmerge[_]%'
AND sysobjects.xtype <> 'S'
AND sysobjects.name not in ('MSsubscription_agents', 'sysmergearticles', 'sysconstraints', 'syssegments')
ORDER BY sysusers.name, sysobjects.xtype, sysobjects.name
GO
--**************************************
--
-- Name: SQL Server Login Info
-- Description:Retrieve the SQL Server l
-- ogin configuration, trusted logins etc.
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/vb/scripts/Sh
-- owCode.asp?txtCodeId=236&lngWId=5--for details.--**************************************
--
USE master
go
IF object_id('sp_Mylogininfo') IS NOT NULL
DROP PROCEDURE sp_Mylogininfo
go
CREATE PROCEDURE sp_Mylogininfo
(
@loginmode varchar(30) = NULL OUTPUT,
@integrated varchar(30) = NULL OUTPUT,
@showdetails bit = 0
)
as
/********************************************************************************/
/* Created BY : Umachandar Jayachandran (UC) */
/* Created ON : 20 October 1996 */
/* Description: This stored PROCEDURE can be used to obtain information */
/* about a SQL Server login. IF executed WITH no parameters*/
/* the stored PROCEDURE will report IF the CURRENT USER IS */
/* connected through standard OR integrated/mixed type */
/* login. The output can also be obtained through variables*/
/* TO make it easy to USE FROM other SPs. The showdetails */
/* flag will display the CURRENT NT logins AND groups which*/
/* have been granted access TO SQL Server. */
/********************************************************************************/
/* Resources : http://www.umachandar.com/resources.htm */
/********************************************************************************/
SET nocount ON
SET ansi_defaults OFF
DECLARE @account varchar(30), @nt_domain varchar(30), @nt_username varchar(30),
@privilege varchar(30), @showresults bit
IF @loginmode IS NULL or @integrated IS NULL
SELECT @showresults = 1
CREATE TABLE #loginconfig (name varchar(30), config_value varchar(30) null)
INSERT #loginconfig EXEC master..xp_loginconfig
SELECT @loginmode = config_value FROM #loginconfig WHERE name = 'login mode'
IF @loginmode = 'standard'
begin
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
CREATE TABLE #logininfo (account varchar(60), type varchar(30),
privilege varchar(30) null, mapped_login varchar(60) null,
permission_path varchar(255) null)
INSERT #logininfo EXEC master..xp_logininfo
DECLARE accounts insensitive CURSOR FOR
SELECT account FROM #logininfo WHERE charindex('SYSTEM', account) = 0
OPEN accounts
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM accounts INTO @account
IF @@fetch_status < 0 BREAK
INSERT #logininfo EXEC master..xp_logininfo @account, 'members'
end
DEALLOCATE accounts
SELECT @nt_domain = nt_domain, @nt_username = nt_username FROM sysprocesses
WHERE spid = @@spid
-- First check for sa
IF suser_id() = 1
begin
-- Next CHECK FOR NT USER privilege
IF exists( SELECT privilege FROM #logininfo
WHERE account = @nt_domain + '\' + @nt_username
AND privilege = 'admin')
SELECT @integrated = 'Yes'
ELSE
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
-- Second check for users
SELECT @integrated = CASE WHEN @nt_username = suser_name() THEN 'Yes' ELSE 'No' END
SHOW_RESULTS_LABEL:
IF @showresults = 1
begin
SELECT @loginmode AS "Server Login Mode",
@integrated AS "Integrated"
end
IF @showdetails = 1
begin
PRINT ''
PRINT 'Server Security Configuration'
SELECT * FROM #loginconfig
PRINT ''
PRINT 'Server Security Details'
SELECT * FROM #logininfo
end
go
GRANT EXECUTE on sp_Mylogininfo TO PUBLIC
go
-- Usage Examples:
PRINT 'With no parameters...'
EXEC sp_Mylogininfo
PRINT ''
go
PRINT 'With show details option...'
EXEC sp_Mylogininfo @showdetails = 1
PRINT ''
go
PRINT 'With output parameters...'
DECLARE @loginmode varchar(30), @integrated varchar(30)
SELECT @loginmode = '', @integrated = ''
EXEC sp_Mylogininfo @loginmode out, @integrated out
SELECT @loginmode AS LoginMode, @integrated as Integrated
sp_Mylogininfo --Return Error 8198 when Execute
USE master
go
IF object_id('sp_Mylogininfo') IS NOT NULL
DROP PROCEDURE sp_Mylogininfo
go
CREATE PROCEDURE sp_Mylogininfo
(
@loginmode varchar(30) = NULL OUTPUT,
@integrated varchar(30) = NULL OUTPUT,
@showdetails bit = 0
)
as
SET nocount ON
SET ansi_defaults OFF
DECLARE @account varchar(30), @nt_domain varchar(30), @nt_username varchar(30),
@privilege varchar(30), @showresults bit
IF @loginmode IS NULL or @integrated IS NULL
SELECT @showresults = 1
CREATE TABLE #loginconfig (name varchar(30), config_value varchar(30) null)
INSERT #loginconfig EXEC master..xp_loginconfig
SELECT @loginmode = config_value FROM #loginconfig WHERE name = 'login mode'
IF @loginmode = 'standard'
begin
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
CREATE TABLE #logininfo (account varchar(60), type varchar(30),
privilege varchar(30) null, mapped_login varchar(60) null,
permission_path varchar(255) null)
INSERT #logininfo EXEC master..xp_logininfo
DECLARE accounts insensitive CURSOR FOR
SELECT account FROM #logininfo WHERE charindex('SYSTEM', account) = 0
OPEN accounts
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM accounts INTO @account
IF @@fetch_status < 0 BREAK
INSERT #logininfo EXEC master..xp_logininfo @account, 'members'
end
DEALLOCATE accounts
SELECT @nt_domain = nt_domain, @nt_username = nt_username FROM sysprocesses
WHERE spid = @@spid
-- First check for sa
IF suser_id() = 1
begin
-- Next CHECK FOR NT USER privilege
IF exists( SELECT privilege FROM #logininfo
WHERE account = @nt_domain + '\' + @nt_username
AND privilege = 'admin')
SELECT @integrated = 'Yes'
ELSE
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
-- Second check for users
SELECT @integrated = CASE WHEN @nt_username = suser_name() THEN 'Yes' ELSE 'No' END
SHOW_RESULTS_LABEL:
IF @showresults = 1
begin
SELECT @loginmode AS "Server Login Mode",
@integrated AS "Integrated"
end
IF @showdetails = 1
begin
PRINT ''
PRINT 'Server Security Configuration'
SELECT * FROM #loginconfig
PRINT ''
PRINT 'Server Security Details'
SELECT * FROM #logininfo
end
go
GRANT EXECUTE on sp_Mylogininfo TO PUBLIC
go
-- Usage Examples:
PRINT 'With no parameters...'
EXEC sp_Mylogininfo
PRINT ''
go
PRINT 'With show details option...'
EXEC sp_Mylogininfo @showdetails = 1
PRINT ''
go
PRINT 'With output parameters...'
DECLARE @loginmode varchar(30), @integrated varchar(30)
SELECT @loginmode = '', @integrated = ''
EXEC sp_Mylogininfo @loginmode out, @integrated out
SELECT @loginmode AS LoginMode, @integrated as Integrated
go
IF object_id('sp_Mylogininfo') IS NOT NULL
DROP PROCEDURE sp_Mylogininfo
go
CREATE PROCEDURE sp_Mylogininfo
(
@loginmode varchar(30) = NULL OUTPUT,
@integrated varchar(30) = NULL OUTPUT,
@showdetails bit = 0
)
as
SET nocount ON
SET ansi_defaults OFF
DECLARE @account varchar(30), @nt_domain varchar(30), @nt_username varchar(30),
@privilege varchar(30), @showresults bit
IF @loginmode IS NULL or @integrated IS NULL
SELECT @showresults = 1
CREATE TABLE #loginconfig (name varchar(30), config_value varchar(30) null)
INSERT #loginconfig EXEC master..xp_loginconfig
SELECT @loginmode = config_value FROM #loginconfig WHERE name = 'login mode'
IF @loginmode = 'standard'
begin
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
CREATE TABLE #logininfo (account varchar(60), type varchar(30),
privilege varchar(30) null, mapped_login varchar(60) null,
permission_path varchar(255) null)
INSERT #logininfo EXEC master..xp_logininfo
DECLARE accounts insensitive CURSOR FOR
SELECT account FROM #logininfo WHERE charindex('SYSTEM', account) = 0
OPEN accounts
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM accounts INTO @account
IF @@fetch_status < 0 BREAK
INSERT #logininfo EXEC master..xp_logininfo @account, 'members'
end
DEALLOCATE accounts
SELECT @nt_domain = nt_domain, @nt_username = nt_username FROM sysprocesses
WHERE spid = @@spid
-- First check for sa
IF suser_id() = 1
begin
-- Next CHECK FOR NT USER privilege
IF exists( SELECT privilege FROM #logininfo
WHERE account = @nt_domain + '\' + @nt_username
AND privilege = 'admin')
SELECT @integrated = 'Yes'
ELSE
SELECT @integrated = 'No'
GOTO SHOW_RESULTS_LABEL
end
-- Second check for users
SELECT @integrated = CASE WHEN @nt_username = suser_name() THEN 'Yes' ELSE 'No' END
SHOW_RESULTS_LABEL:
IF @showresults = 1
begin
SELECT @loginmode AS "Server Login Mode",
@integrated AS "Integrated"
end
IF @showdetails = 1
begin
PRINT ''
PRINT 'Server Security Configuration'
SELECT * FROM #loginconfig
PRINT ''
PRINT 'Server Security Details'
SELECT * FROM #logininfo
end
go
GRANT EXECUTE on sp_Mylogininfo TO PUBLIC
go
-- Usage Examples:
PRINT 'With no parameters...'
EXEC sp_Mylogininfo
PRINT ''
go
PRINT 'With show details option...'
EXEC sp_Mylogininfo @showdetails = 1
PRINT ''
go
PRINT 'With output parameters...'
DECLARE @loginmode varchar(30), @integrated varchar(30)
SELECT @loginmode = '', @integrated = ''
EXEC sp_Mylogininfo @loginmode out, @integrated out
SELECT @loginmode AS LoginMode, @integrated as Integrated
Discover the name of the Windows group that allowed you to connect
If you need name of the Windows group that allowed you to connect:
==================================================================
If you need name of the Windows group that allowed the user to connect, take
a look at xp_logininfo and the 'mapped user name' column in the result.
You'll need to insert the results into a table like the example below if you
need a scalar result. See xp_logininfo in the Books Online for permission
requirements.
-------------------------------------------:
http://www.megasolutions.net/Sqlserver/Using-suser_id()-and-sys_server_principals-when-connecting-through-a-windows-group-7608.aspx
SET NOCOUNT ON
DECLARE
@me sysname,
@permission_path sysname
SET @me = SUSER_SNAME()
CREATE TABLE #LoginInfo
(
account_name sysname,
logintype char(8),
privilege char(9),
mapped_login_name sysname,
permission_path sysname
)
INSERT INTO #LoginInfo
EXEC xp_logininfo @me
SELECT @permission_path = permission_path
FROM #LoginInfo
DROP TABLE #LoginInfo
SELECT @permission_path
GO
==================================================================
If you need name of the Windows group that allowed the user to connect, take
a look at xp_logininfo and the 'mapped user name' column in the result.
You'll need to insert the results into a table like the example below if you
need a scalar result. See xp_logininfo in the Books Online for permission
requirements.
-------------------------------------------:
http://www.megasolutions.net/Sqlserver/Using-suser_id()-and-sys_server_principals-when-connecting-through-a-windows-group-7608.aspx
SET NOCOUNT ON
DECLARE
@me sysname,
@permission_path sysname
SET @me = SUSER_SNAME()
CREATE TABLE #LoginInfo
(
account_name sysname,
logintype char(8),
privilege char(9),
mapped_login_name sysname,
permission_path sysname
)
INSERT INTO #LoginInfo
EXEC xp_logininfo @me
SELECT @permission_path = permission_path
FROM #LoginInfo
DROP TABLE #LoginInfo
SELECT @permission_path
GO
Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
--------------------------------------------------------------------
http://support.microsoft.com/kb/272318
When DBCC SHRINKFILE is run, SQL Server 2000 shrinks the log file by removing as many virtual log files as it can to attempt to reach the target size. If the target file size is not reached, SQL Server places dummy log entries in the last virtual log file until the virtual log is filled and moves the head of the log to the beginning of the file. The following actions are then required to complete the shrinking of the transaction log:
You must run a BACKUP LOG statement to free up space by removing the inactive portion of the log.
You must run DBCC SHRINKFILE again with the desired target size until the log file shrinks to the target size.
The following example demonstrates this with the pubs database and attempts to shrink the pubs_log file to 2 MB:
Run this code:
DBCC SHRINKFILE(pubs_log, 2)
NOTE: If the target size is not reached, proceed to the next step.
Run this code if you want to truncate the transaction log and not keep a backup of the transaction log. Truncate_only invalidates your transaction log backup sequence. Take a full backup of your database after you perform backup log with truncate_only:
BACKUP LOG pubs WITH TRUNCATE_ONLY
-or-
Run this code if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact. See SQL Server Books Online topic "BACKUP" for more information:
BACKUP LOG pubs TO pubslogbackup
Run this code:
DBCC SHRINKFILE(pubs_log,2)
--------------------------------------------------------------------
http://support.microsoft.com/kb/272318
When DBCC SHRINKFILE is run, SQL Server 2000 shrinks the log file by removing as many virtual log files as it can to attempt to reach the target size. If the target file size is not reached, SQL Server places dummy log entries in the last virtual log file until the virtual log is filled and moves the head of the log to the beginning of the file. The following actions are then required to complete the shrinking of the transaction log:
You must run a BACKUP LOG statement to free up space by removing the inactive portion of the log.
You must run DBCC SHRINKFILE again with the desired target size until the log file shrinks to the target size.
The following example demonstrates this with the pubs database and attempts to shrink the pubs_log file to 2 MB:
Run this code:
DBCC SHRINKFILE(pubs_log, 2)
NOTE: If the target size is not reached, proceed to the next step.
Run this code if you want to truncate the transaction log and not keep a backup of the transaction log. Truncate_only invalidates your transaction log backup sequence. Take a full backup of your database after you perform backup log with truncate_only:
BACKUP LOG pubs WITH TRUNCATE_ONLY
-or-
Run this code if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact. See SQL Server Books Online topic "BACKUP" for more information:
BACKUP LOG pubs TO pubslogbackup
Run this code:
DBCC SHRINKFILE(pubs_log,2)
Abonneren op:
Posts (Atom)