Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
how to list login and db user names
- Thread starter IT247
- Start date
ptheriault
IS-IT--Management
What version of SQL server are you using?
- Thread starter
- #3
ptheriault
IS-IT--Management
This procs I have will help you.
CREATE PROCEDURE spshowperms
@username sysname = NULL
as
/* Show table permission for database user RES -- 2006-06-19 */
select distinct a.name as username,c.name as tablename,e.name as action
from sysusers a,syspermissions b,sysobjects c,sysprotects d, master.dbo.spt_values e
where a.uid=b.grantee
and a.name = @username
and b.id=c.id
and c.id=d.id
and a.uid=d.uid
and d.action = e.number
and e.type = 'T'
order by a.name,c.name,e.name
GO
declare @username varchar(255)
select @username = 'LoginWithAProblem'
select distinct e.name as action,c.name as tablename,a.name as username
into #permissionTable
from sysusers a,syspermissions b,sysobjects c,sysprotects d,
master.dbo.spt_values e
where a.uid=b.grantee
and a.name = @username
and b.id=c.id
and c.id=d.id
and a.uid=d.uid
and d.action = e.number
and e.type = 'T'
order by a.name,c.name,e.name
select 'Grant ' + [action] + ' on ' + tablename + ' to ' + username from
#permissionTable
--Run these results to set permissions for tables and views.
CREATE PROCEDURE spshowperms
@username sysname = NULL
as
/* Show table permission for database user RES -- 2006-06-19 */
select distinct a.name as username,c.name as tablename,e.name as action
from sysusers a,syspermissions b,sysobjects c,sysprotects d, master.dbo.spt_values e
where a.uid=b.grantee
and a.name = @username
and b.id=c.id
and c.id=d.id
and a.uid=d.uid
and d.action = e.number
and e.type = 'T'
order by a.name,c.name,e.name
GO
declare @username varchar(255)
select @username = 'LoginWithAProblem'
select distinct e.name as action,c.name as tablename,a.name as username
into #permissionTable
from sysusers a,syspermissions b,sysobjects c,sysprotects d,
master.dbo.spt_values e
where a.uid=b.grantee
and a.name = @username
and b.id=c.id
and c.id=d.id
and a.uid=d.uid
and d.action = e.number
and e.type = 'T'
order by a.name,c.name,e.name
select 'Grant ' + [action] + ' on ' + tablename + ' to ' + username from
#permissionTable
--Run these results to set permissions for tables and views.
- Thread starter
- #5
ptheriault
IS-IT--Management
This will create a procdure that will show you users and their permissions for each db. If you create the procedure as is you will get back a script that creates permissions for each login. I don't think you want that. So you will need to comment out the following section
select 'Grant ' + [action] + ' on ' + tablename + ' to ' + username from
#permissionTable
--Run these results to set permissions for tables and views.
and replace it with.
SELECT * FROM #permissionTable
You will need to create this in each db you want to know the permissions for.
The first time you run this in QA it will create the procedure. After you create it you will want to execute it.
exec spshowperms
select 'Grant ' + [action] + ' on ' + tablename + ' to ' + username from
#permissionTable
--Run these results to set permissions for tables and views.
and replace it with.
SELECT * FROM #permissionTable
You will need to create this in each db you want to know the permissions for.
The first time you run this in QA it will create the procedure. After you create it you will want to execute it.
exec spshowperms
- Thread starter
- #7
thx P
i am having trouble with it.
first i created it in master db, ran it and it said it could not find the proc.
then i deleted it and created it in my own p7dr db
and it said 1)THE table permission table already existed
and
2)the proc already exists - as if it didn't really delete it
by the way, when i do select current_user it says dbo
i expected to see my name????
i am having trouble with it.
first i created it in master db, ran it and it said it could not find the proc.
then i deleted it and created it in my own p7dr db
and it said 1)THE table permission table already existed
and
2)the proc already exists - as if it didn't really delete it
by the way, when i do select current_user it says dbo
i expected to see my name????
ptheriault
IS-IT--Management
IT247,
I found this script. It works better and you don't have to create the proc.
sp_msforeachdb 'use ?
if exists (select 1
from sysmembers
where user_name(memberuid) not in (''dbo'',''guest'')
and user_name(groupuid) in (''db_owner'',''db_datareader'',''db_datawriter''))
begin
select cast(db_name() as varchar(30)) as [Database Name]
select case when grouping(groupuid) = 0 and grouping(memberuid) = 1
then cast(user_name(groupuid) as varchar(30))
else ''''
end as [Role],
case when grouping(groupuid) = 0 and grouping(memberuid) = 0
then cast (user_name(memberuid) as varchar(40))
else ''''
end as [member]
from dbo.sysmembers m
where user_name(memberuid) not in (''dbo'',''guest'')
and (user_name(groupuid) like ''db_%'')
or exists (select 1 from sysmembers
where memberuid = m.groupuid
and user_name(groupuid) like ''db_%'')
group by groupuid, memberuid
with rollup
order by groupuid, memberuid, grouping(groupuid) desc, grouping(memberuid)
end'
I found this script. It works better and you don't have to create the proc.
sp_msforeachdb 'use ?
if exists (select 1
from sysmembers
where user_name(memberuid) not in (''dbo'',''guest'')
and user_name(groupuid) in (''db_owner'',''db_datareader'',''db_datawriter''))
begin
select cast(db_name() as varchar(30)) as [Database Name]
select case when grouping(groupuid) = 0 and grouping(memberuid) = 1
then cast(user_name(groupuid) as varchar(30))
else ''''
end as [Role],
case when grouping(groupuid) = 0 and grouping(memberuid) = 0
then cast (user_name(memberuid) as varchar(40))
else ''''
end as [member]
from dbo.sysmembers m
where user_name(memberuid) not in (''dbo'',''guest'')
and (user_name(groupuid) like ''db_%'')
or exists (select 1 from sysmembers
where memberuid = m.groupuid
and user_name(groupuid) like ''db_%'')
group by groupuid, memberuid
with rollup
order by groupuid, memberuid, grouping(groupuid) desc, grouping(memberuid)
end'
ptheriault
IS-IT--Management
By the way. If you are an 'sa' I believe current_user will show you as dbo.
- Thread starter
- #10
i'll give that a try
hey, are u saying that 'sa' is a role?
i thought it was just one special user.
but where do you set the default login. That is, if i am logged in to with my domain acct, then i open sql (mixed mode auth)will it just go and use my window login?
let's say i want to login as a different user?
hey, are u saying that 'sa' is a role?
i thought it was just one special user.
but where do you set the default login. That is, if i am logged in to with my domain acct, then i open sql (mixed mode auth)will it just go and use my window login?
let's say i want to login as a different user?
ptheriault
IS-IT--Management
Yes, there is a role system adminitrator that you can add logins to. 'sa' is also a login. In SQL server 2000 the only way to change who you are logged in as is to exit and edit your properties of Enterprise Manager or login usinga different SQL account in QA. If you want to use a different NT account then you would need to log off your PC and login with the other account.
In SQL server 2005 you can actually execute statements with the credentials from another account. The syntax is
EXECUTE AS USER = 'user'
SELECT...
In SQL server 2005 you can actually execute statements with the credentials from another account. The syntax is
EXECUTE AS USER = 'user'
SELECT...
- Thread starter
- #12
sorry but like i said i am just getting started with sql
i tried the script
i just pasted it into QA, higlighted it and ran it.
it output info about one particular db. how do i do it for all?
do i need an 'exec' in front of first statement?
do i need a 'go' anywhere?
i tried the script
i just pasted it into QA, higlighted it and ran it.
it output info about one particular db. how do i do it for all?
do i need an 'exec' in front of first statement?
do i need a 'go' anywhere?
ptheriault
IS-IT--Management
Don't be sorry. We all start somewhere. It should have run for each db. That is what the command sp_msforeachdb 'use ? does. it replaces the '?' with the name of your db and it does it for each db. When execute it I get results for all databases.
- Thread starter
- #14
ptheriault
IS-IT--Management
if it is the only query in your window you don't need to highlight it. It doesn't matter which db you are in. Try it from another on. But it sounds like you did it correct. Are you a member of the 'sa' role or logged in as 'sa'?
- Thread starter
- #16
well that's part of my confusion
i just have two servers showing in EM. The props on the first db says "start and run sql server in the following acct" --- tce\sqlserver (tce is my domain)
the 2nd db says tce\mac (mac being my domain user id)
so when i open up EM, i don't know what's being used???
i just have two servers showing in EM. The props on the first db says "start and run sql server in the following acct" --- tce\sqlserver (tce is my domain)
the 2nd db says tce\mac (mac being my domain user id)
so when i open up EM, i don't know what's being used???
ptheriault
IS-IT--Management
The start and run setting are not your login credentials. If you want to set your login credentials right click on the server name in EM and select EDIT Server Registration. This will change how the server is registered in your Enterprise Manager Console.
By the way..It looks like the second server is running with your windows credentials. That not such a good idea. If you change your password it will lock you out.
By the way..It looks like the second server is running with your windows credentials. That not such a good idea. If you change your password it will lock you out.
- Thread starter
- #18
ptheriault
IS-IT--Management
That is because the account your are now using is not a system admin. Go back to 'sa' account and add the roles you want to the account you wish to use.
SQL Server runs as a service. If you open up the service program you can view the login information for that instance of SQL. If it is using your Windows account as you said for server2 and you change that password on the domain then the passwords will not match and your server will fail to start next time you restart it. it will also most likely lock out your account.
SQL Server runs as a service. If you open up the service program you can view the login information for that instance of SQL. If it is using your Windows account as you said for server2 and you change that password on the domain then the passwords will not match and your server will fail to start next time you restart it. it will also most likely lock out your account.
- Thread starter
- #20
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question
