Re: Little database user account script need help...

Erland,

Ah. No it doesn't work for me because I have set @sql = 'DENY VIEW ANY DATABASE TO ' + QUOTENAME(@login_name)

is there a way to change it to be:

GRANT VIEW db_name to login_name ?

I've tried like 30 different combinations and looked in the books online documentation and it didnt seem to work, and no matter how I word the script it always creates the DB with DBO = administrator
"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message news:Xns9A7185E154445Yazorman@xxxxxxxxxxxx
Uri Dimant (urid@xxxxxxxxxxx) writes:
Erland
SELECT @sp_addrolemember = quotename(@db_name) + '.sys.sp_addrolemember
EXEC @sp_addrolemember db_owner, @login_name

How does this script work? I cannot run it on SS2005 sp2

There is a single quote missing on the first line.

The tricks are two:

1) EXEC @var runs the procedure in @var
2) EXEC some_db..sp_systemproc, runs that system procedure in the context of
the some_db database.

What happened when you tried the above?
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: How do i move an SQLDatabase to another location?
    ... I went to my clients and installed SQL Server ... Express and copied my database to the same location it was in while i was ... knowing that i can bring a database with me and install ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: New to Windows CE Development - Have some questions
    ... validating against the main database, or querying the database during data ... iterate through the SQL CE table and create new entries in the ... SQL Server database that correspond. ... >>> not connected to the Enterprise. ...
    (microsoft.public.windowsce.app.development)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • RE: SBS 2003 Unable to connect to database STS_Config
    ... MSDE to store SharePoint uses the MSSQL$SharePoint database, ... SharePoint named instance to full SQL. ... You cannot upgrade the Monitoring instance to SQL ... What I see is the SQL server group and under ...
    (microsoft.public.windows.server.sbs)
  • Re: Full text catalog just not populating
    ... exec sp_defaultdb N'NT Authority\System', N'master' ... means either the network guys in my company who don't know SQL but are admins ... > needs this login to log into SQL Server and you can either add back this ... >> fetching U ...
    (microsoft.public.sqlserver.fulltext)