how to list login and db user names

[IT247]

hello
is there a storedproc that will output the login names and the associated users for each database?

thx

 

[ptheriault]

What version of SQL server are you using?

 

[IT247]

sql 2000

 

[ptheriault]

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.

 

[IT247]

i'm new to this - what does this code do
can i run in sql query analyzer?

 

[ptheriault]

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

 

[IT247]

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

 

[ptheriault]

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'

 

[ptheriault]

By the way. If you are an 'sa' I believe current_user will show you as dbo.

 

[IT247]

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?

 

[ptheriault]

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...

 

[IT247]

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?

 

[ptheriault]

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.

 

[IT247]

did i run it correctly by highlighting and clicking the 'execute query' button on top?
does the db have to be on 'master'?
all it does is return info for 1 db

 

[ptheriault]

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

 

[IT247]

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

 

[ptheriault]

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.

 

[IT247]

u mean if i change my windows password?
i just edited the server regist login to system
it asked if i wanted to disconnect
when i go back in, i noticed i could not open any login object and right click menu an option is greyed out

 

[ptheriault]

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.

 

[IT247]

ptheriault
thx so much for your patience sir
i appreciate it.

what is the best practice:
for the server should i use sql auth or windows (and what account should i use anyways?)