Environment users and logins

Hi
We are currently migrating from MSSQL 2000 enterprice to MSSQL 2005
Enterprice, and with that doing a revision of our users.
We would like to be able to do a restore a backup of Production to test.
We would like user privileges on the servers to differ so a given user can
execute some stored procedures on one environment but not the other. Say, we
don’t want a service in test with the wrong connection string to be have
access to production.
We have previously done this by using local user groups on the servers, but
we loose the mapping between the Login name and the user name when we do the
restores.
Any ideas or best practices?
Best regards
MikaelMikael
> We would like to be able to do a restore a backup of Production to test.
No problem, just RESTORE command

> We would like user privileges on the servers to differ so a given user can
> execute some stored procedures on one environment but not the other. Say,
> we
> dont want a service in test with the wrong connection string to be have
> access to production.
MS has introduced in SS2005 lots of new features, like SCHEMA for example.
I'd suggets you to spend a hew days to study it

> We have previously done this by using local user groups on the servers,
> but
> we loose the mapping between the Login name and the user name when we do
> the
> restores.
http://blogs.msdn.com/lcris/archive.../03/567680.aspx
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:48261D3D-86AC-4B6F-8B0A-380101564866@.microsoft.com...
> Hi
> We are currently migrating from MSSQL 2000 enterprice to MSSQL 2005
> Enterprice, and with that doing a revision of our users.
> We would like to be able to do a restore a backup of Production to test.
> We would like user privileges on the servers to differ so a given user can
> execute some stored procedures on one environment but not the other. Say,
> we
> dont want a service in test with the wrong connection string to be have
> access to production.
> We have previously done this by using local user groups on the servers,
> but
> we loose the mapping between the Login name and the user name when we do
> the
> restores.
> Any ideas or best practices?
>
> --
> Best regards
> Mikael

Enumerate SQL Server Logins and Permissions

Haven't been able to find the answer to this after sever searches. So decided to open the thread myself.

I am trying to write a report enumerating logins in SQL Server and all database-level and object-level permissions granted to each login.

Can someone tell me what system objects I can query to fetch this information?

TIA.

Hi,

You can get the results by querying the following system views(cata log views):

Database-Level Views:

sys.database_permissions
sys.database_principals

Server-Level Views:

sys.server_permissions
sys.sql_logins
sys.server_principals

Thanks & Regards,

Kiran.Y

|||

That's just what I was looking for. Thanks much.

sql

Enterprise Mgr - Register server with multiple logins?

How can I register the same SQL Server more than once in
Enterprise Manager, using multiple logins?
"A server with this name already exists."
I'd expect this to be a FAQ, but twenty minutes of
googling it has turned up precious little by way of
remedies. This should be a pretty garden-variety
situation: I need to access multiple databases on a single
SQL Server, some of which require a different login from
others. I've added a new server group for the additional
instance/connection/registration, but every attempt to
register the server returns "A server with this name
already exists."
This is a simple thing to accomplish in Query Analyzer,
but I need some of Enterprise Manager's functionality.
Obviously, I could keep deleting and re-registering every
time I need to change context, but that's a pretty tedious
kludge, or I wouldn't be writing this post, you know?
Anyways, is there a hack?
Thanks much.
Create an alias to the SQL Server within the Client Network Utility (within
the SQL Server program group) and then register that alias within Enterprise
Manager.
Keith
"Paul Smith" <paul.smith@.zurichna.com> wrote in message
news:18f301c4a648$0f3dc8e0$a601280a@.phx.gbl...
> How can I register the same SQL Server more than once in
> Enterprise Manager, using multiple logins?
> "A server with this name already exists."
> I'd expect this to be a FAQ, but twenty minutes of
> googling it has turned up precious little by way of
> remedies. This should be a pretty garden-variety
> situation: I need to access multiple databases on a single
> SQL Server, some of which require a different login from
> others. I've added a new server group for the additional
> instance/connection/registration, but every attempt to
> register the server returns "A server with this name
> already exists."
> This is a simple thing to accomplish in Query Analyzer,
> but I need some of Enterprise Manager's functionality.
> Obviously, I could keep deleting and re-registering every
> time I need to change context, but that's a pretty tedious
> kludge, or I wouldn't be writing this post, you know?
> Anyways, is there a hack?
> Thanks much.
>
|||That hit the spot - forgot all about the Client Utility, but what a simple,
quick and straightforward solution (...which is probably why I didn't arrive
at it on my own! heh.)
Thanks.
"Keith Kratochvil" wrote:

> Create an alias to the SQL Server within the Client Network Utility (within
> the SQL Server program group) and then register that alias within Enterprise
> Manager.
> --
> Keith
>
> "Paul Smith" <paul.smith@.zurichna.com> wrote in message
> news:18f301c4a648$0f3dc8e0$a601280a@.phx.gbl...
>